MAT Calculation in Power BI

[mayankkverma95]

Hi Everyone,

I got stuck while calculating MAT (Moving Annual Total) in my report.

Data Scenario- I have data which is only for latest period. Below is a raw snap:

ProductID	Date	Current Values	Previous Values	Previous to Previous Values
PP1	Jan-21	100	50	60
PP2	Jan-21	200	40	70
PP3	Feb-21	300	70	300
PP4	Feb-21	250	900	400
PP5	Mar-21	350	40	200

First Column is my Product ID which is sold out, second column is my current year date, third column represent selling amount in the respective current year date. Fourth column is previous year selling amount for previous year date. For example in Row1, against Jan 2021, $ 200 selling recorded and $ 50 selling recorded in Jan 2020 and $ 60 recorded in 2019.

Now, I want to calculate MAT (for last 12 months, basis current selection of month).
I have created a Calendar table also in Power BI. But seems it is not working somehow.
Can you please provide the solution to this specific problem of calculating MAT given above my data structure.
Thank you!!
@Jimmy801 

[amitchandak]

@mayankkverma95 , Not vey clear with help from date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

2nd Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))

[mayankkverma95]

Hi,
This solution will work only if i have all dates in Fact table (Including current and previous years). I have Only current year date in Fact Table. But for previous year values, I have those in third and fourth columns.

[v-yiruan-msft · Accepted solution]

Hi @mayankkverma95 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Make a data conversion so that the values of different years are displayed in the same column and the corresponding dates are added respectively as shown in below screenshot

2. Create a measure as below to get MAT just as suggested by @amitchandak 

MTD = 
CALCULATE (
    SUM ( 'Table'[Values] ),
    DATESYTD ( DATEADD ( 'Date'[Date], -1, YEAR ), "12/31" )
)

In addition, please review the content in the following video. Help it can help you~

Power BI M Code for Moving Annual Total (MAT)

Best Regards