Forum Discussion

mjr0013's avatar
mjr0013
Frequent Visitor
4 years ago

Creating Y-o-Y % Change using DAX

I am trying to create a year over year % change using DAX but the issue I am encoutering is my date table is not the traditional date table due to how the excel file extracts information. I have DAX ...
Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

I believe you can use this pattern for MTD LY. You would substitute your table name(with year and month no) for Calendar as used below. You will see the use of taking 1 away from year variable to get to LY. I hope this helps..

Previous Month Sales =
VAR CurrentMonth = SELECTEDVALUE( 'Calendar'[Fin Month Number] )
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[Fin Year] )
VAR MaxMonthNum = CALCULATE( MAX( 'Calendar'[Fin Month Number] ), ALL( 'Calendar' ) )

RETURN
IF( HASONEVALUE( 'Calendar'[Fin Month Number] ),
SUMX(
    FILTER( ALL( 'Calendar' ),
        IF( CurrentMonth = 1,
        'Calendar'[Fin Month Number] = MaxMonthNum && 'Calendar'[Fin Year] = CurrentYear - 1,
        'Calendar'[Fin Month Number] = CurrentMonth - 1 && 'Calendar'[Fin Year] = CurrentYear )),
        [Total Sales] ),
BLANK() )
mjr0013's avatar
mjr0013
Frequent Visitor
4 years ago

I had the following error saying a single Month # cannot be determined but I am confused how this could be the case? The screenshots below are as follows... 1) The measure I am attempting (and failing) 2) what my "Date Table" looks like and 3) the sorting table you see in the last column of the second one.