4 Years MAT calculation with YOY change

[enghone09]

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?

[ERD · Accepted solution]

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]

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]

@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]

@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]

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

[amitchandak]

@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]

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?