Forum Discussion

mayankkverma95's avatar
mayankkverma95
Regular Visitor
5 years ago
Solved

MAT Calculation in Power BI

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 Valu...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
mayankkverma95
Regular Visitor
5 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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