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 li...
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.

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.

  • enghone09's avatar
    enghone09
    Frequent Visitor
    4 years ago

    ERD  Thank you so much. It is all working now after adding a date table. 

     

    May I ask why we need a date table? Is it not possible to achieve without a date table?

     

    I am still learning on this. 

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

      enghone09 ,

      Having a separate Date table is considered to be one of best practices. Moreover, Date table is mandatory for many Time intelligence functions that you might need in future.

      You can read this article: https://kteam.ch/why-almost-every-power-bi-report-needs-a-date-table/ 

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