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.
Community ChampionHi 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.
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.
