Forum Discussion
4 Years MAT calculation with YOY change
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.
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.
Community ChampionHaving 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.