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:

ProductIDDateCurrent ValuesPrevious ValuesPrevious to Previous Values
PP1Jan-211005060
PP2Jan-212004070
PP3Feb-2130070300
PP4Feb-21250900400
PP5Mar-2135040200

 

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 

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

3 Replies

  • 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