DAX for % change of Previous and Prioor Privious Month

[mwasekaar]

I Had to show Top 1 category in my KPI card with respect to sales .Which I shown using TopN function. Now I want to show % change for this category for Previous and Prior Previous Month such that current month shall be excluded . meaning in my dataset which is a monthly sales having 12 Month Data from 1 Jan 2024 to 10 Dec 2024 then Dec month should exclude which doing % calculation . I stucked in writing DAX please help me 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I tried using INDEX DAX function.

INDEX function (DAX) - DAX | Microsoft Learn

Sales: = 
SUM(sales[sales])

latest year-month sales: = 
VAR _saleslatestdate =
    MAXX (
        CALCULATETABLE (
            SUMMARIZE ( sales, 'calendar'[Date] ),
            REMOVEFILTERS ( 'calendar' )
        ),
        'calendar'[Date]
    )
VAR _endofmonth =
    EOMONTH ( _saleslatestdate, 0 )
VAR _yearmonth =
    FILTER (
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        'calendar'[Year-Month sort] <= _endofmonth
    )
VAR _t =
    INDEX ( 1, _yearmonth, ORDERBY ( 'calendar'[Year-Month sort], DESC ) )
RETURN
    CALCULATE ( [Sales:], _t )

previous year-month sales: = 
VAR _saleslatestdate =
    MAXX (
        CALCULATETABLE (
            SUMMARIZE ( sales, 'calendar'[Date] ),
            REMOVEFILTERS ( 'calendar' )
        ),
        'calendar'[Date]
    )
VAR _endofmonth =
    EOMONTH ( _saleslatestdate, 0 )
VAR _yearmonth =
    FILTER (
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        'calendar'[Year-Month sort] <= _endofmonth
    )
VAR _t =
    INDEX ( 2, _yearmonth, ORDERBY ( 'calendar'[Year-Month sort], DESC ) )
RETURN
    CALCULATE ( [Sales:], _t )

prior previous year-month sales: = 
VAR _saleslatestdate =
    MAXX (
        CALCULATETABLE (
            SUMMARIZE ( sales, 'calendar'[Date] ),
            REMOVEFILTERS ( 'calendar' )
        ),
        'calendar'[Date]
    )
VAR _endofmonth =
    EOMONTH ( _saleslatestdate, 0 )
VAR _yearmonth =
    FILTER (
        ALL ( 'calendar'[Year-Month], 'calendar'[Year-Month sort] ),
        'calendar'[Year-Month sort] <= _endofmonth
    )
VAR _t =
    INDEX ( 3, _yearmonth, ORDERBY ( 'calendar'[Year-Month sort], DESC ) )
RETURN
    CALCULATE ( [Sales:], _t )

[techies]

Hi @mwasekaar can you please share the DAX you are using for top category? just want to align further