Year to Date % change calculation problem

[Anonymous]

Hey!

I am trying to calculate a measure that takes the sum of values from last years max month (December) and compares it against this years latest visible month sum and then calculates the % change between these two value. The current year returns a correct value but previous year returns blank. Previous year and max month variables return correct values but when I wrap this up in a calculation it stops working. I've been stuck here for quite a some time so help is highly appreciated. Here is the DAX measure that I have created.

Fleet Growth % =
VAR current_year =
    SELECTEDVALUE ( vDimCalendar[Year] )
VAR _LATEST =
    CALCULATE (
        SUM ( Assets[TotalAssets] ),
        FILTER (
            vDimCalendar,
            vDimCalendar[Month] = MAX ( vDimCalendar[Month] )
                && vDimCalendar[Year] = current_year
        )
    )

VAR previous_year = current_year - 1

VAR max_month =
    MAXX ( vDimCalendar, vDimCalendar[MonthNumber] )

VAR _PREVIOUS =
    CALCULATE (
        SUM ( Assets[TotalAssets] ),
        FILTER (
            vDimCalendar,
            ( vDimCalendar[MonthNumber] = max_month )
                && ( vDimCalendar[Year] = previous_year )
        )
    )
RETURN
    DIVIDE ( _LATEST - _PREVIOUS, _PREVIOUS, 0 )

[ValtteriN]

Hi,

Here is how to do this:

Data:

Dax:

Measure 9 =

var _thisyearmax = CALCULATE(SUM('Table (16)'[Value]),ALL('Calendar'[Date]),'Calendar'[Month]=MONTH(MAX('Table (16)'[Date])),'Calendar'[Year]=YEAR(TODAY()))

var _lastyearmax = CALCULATE(SUM('Table (16)'[Value]),ALL('Calendar'[Date]),'Calendar'[Month]=12,'Calendar'[Year]=YEAR(TODAY())-1)

return

DIVIDE(_thisyearmax-_lastyearmax,_lastyearmax,0)

Since last year's max month is always 12 I am using that here. 


Now if we validate the results: (40-30)/(30) = 0,33

Second test:
(50-30)/(30)= 0.67



I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/