Forum Discussion

enghone09's avatar
enghone09
Frequent Visitor
4 years ago
Solved

4 Years MAT calculation with YOY change

Hello everyone,

 

I have just started POWER BI and struggling to find a solution for moving annual total.

 

I currently have over 4 years of data from (01 Jan 2016 to 30 Jul 2021). and I would like to compare the last 4 MAT; 

  • MAT Jul 2021 (Aug 20 to Jul 2021)
  • MAT Jul 2020 (Aug 19 to Jul 2020)
  • MAT Jul 2019 (Aug 18 to Jul 2019)
  • MAT Jul 2018 (Aug 17 to Jul 2018)

Since 2021 isn't finished, every time I do YOY% calculation, the 2021 % will be down by 40%. 

 

How do I go about solving this issue to only compare the same period in the previous years? As the year progress, there will be more monthly data i.e. MAT Jul 2021 becomes MAT August 2021 once August data become available?

 

Is it best to build a MAT period in the calendar table or by DAX during calculation? Could someone please kindly guide me on this?

 

Need Help
  • ERD's avatar
    ERD
    4 years ago

    Hi enghone09 ,

    From the example you've provided as far as I understand you need to achieve this:

    Measures:

    MATvalue = 
VAR currentYear = MAX ( 'Date'[Year] )
VAR latestDay = DAY ( MAXX ( ALL ( T6[Date] ), T6[Date] ) )
VAR latestMonth = MONTH ( MAXX ( ALL ( T6[Date] ), T6[Date] ) )
VAR MATDate = DATE ( currentYear, latestMonth, latestDay )
VAR firstDay = EDATE ( MATDate + 1, -12 )
RETURN
    CALCULATE (
        SUM ( T6[No. of Services] ),
        'Date'[Date] >= firstDay,
        'Date'[Date] <= MATDate
    )

    To have correct totals:

    MATresult = 
IF (
    HASONEVALUE ( 'Date'[Year] ),
    [MATvalue],
    SUMX ( VALUES ( 'Date'[Year] ), [MATvalue] )
)

    MAT%:

    MAT% = 
VAR currentValue = [MATvalue]
VAR previousValue = CALCULATE ( [MATvalue], DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    IF (
        HASONEVALUE ( 'Date'[Year] ),
        DIVIDE ( currentValue - previousValue, previousValue ),
        BLANK ()
    )

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    enghone09 , Not very clear, but something like with YTD

     

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

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

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

     


    Power BI — Year on Year with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
    https://www.youtube.com/watch?v=km41KfM_0uA

    • enghone09's avatar
      enghone09
      Frequent Visitor
      4 years ago

      Hi amitchandak ,

      Thank you for your response. I have attached my sample data. 

      Excel DATA 

       

      - How can I change the above dax formula to be dynamic without having to change the date from "7/31" to "8/31" once the data become available?

      - Is there a DAX formula that can calculate all 3 years in one measure? 

       

       


       

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi enghone09 ,

    From the example you've provided as far as I understand you need to achieve this:

    Measures:

    MATvalue = 
VAR currentYear = MAX ( 'Date'[Year] )
VAR latestDay = DAY ( MAXX ( ALL ( T6[Date] ), T6[Date] ) )
VAR latestMonth = MONTH ( MAXX ( ALL ( T6[Date] ), T6[Date] ) )
VAR MATDate = DATE ( currentYear, latestMonth, latestDay )
VAR firstDay = EDATE ( MATDate + 1, -12 )
RETURN
    CALCULATE (
        SUM ( T6[No. of Services] ),
        'Date'[Date] >= firstDay,
        'Date'[Date] <= MATDate
    )

    To have correct totals:

    MATresult = 
IF (
    HASONEVALUE ( 'Date'[Year] ),
    [MATvalue],
    SUMX ( VALUES ( 'Date'[Year] ), [MATvalue] )
)

    MAT%:

    MAT% = 
VAR currentValue = [MATvalue]
VAR previousValue = CALCULATE ( [MATvalue], DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    IF (
        HASONEVALUE ( 'Date'[Year] ),
        DIVIDE ( currentValue - previousValue, previousValue ),
        BLANK ()
    )

    If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

    • enghone09's avatar
      enghone09
      Frequent Visitor
      4 years ago

      Hi ERD 

       

      Did you use a separate calendar table? I am getting a different result for MAT results. Please see below. 

       

      Do you mind sharing your PBIX file?

       

       

       

       

       

       

      • ERD's avatar
        ERD
        Icon for Community Champion rankCommunity Champion
        4 years ago

        enghone09 , cannot share files, but as for the Date table, yes, I use a separate one. This table is connected to your data table (Date column). You can easily create a Date table in Power Query or DAX.

        If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.