12 months trailing measure

[Yuziu8o]

I need a KPI for management that shows rolling 12 month sales based on the last visible date in the report. Along with that, I also need the year over year percent change for the same rolling 12 month window. I have a proper date table and a Total Sales measure, but I am not sure how to structure the DAX to handle the rolling window and the YoY comparison. What is the right way to write these measures so they work at any date granularity?

[Selva-Salimi · Accepted solution]

Hi @Yuziu8o ,

you can write measures as follow:

rolling_month := calculate(sum(sales), filter (all(table) , date >= EDATE (max(date) , -12)))

YOY := divide ( calculate(sum(sales), filter (all(table) , date >= EDATE(EDATE (max(date) , -12),-12) && date < EDATE(max(date), -12))) , calculate(sum(sales), filter (table , date >= EDATE (max(date) , -12))))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.

[Shubham_rai955 · Accepted solution]

You can build a rolling 12 month KPI with three measures. Each one anchors to the last visible date in the current filter context, so it works at any granularity.

Rolling 12 Month Sales

 

Rolling 12M Sales = CALCULATE( [Total Sales], DATESINPERIOD( 'Date'[Date], MAX('Date'[Date]), -12, MONTH ) )

Rolling 12 Month Sales YoY

 

Rolling 12M Sales YoY = CALCULATE( [Rolling 12M Sales], DATEADD('Date'[Date], -1, YEAR) ) )

Rolling 12 Month YoY Percent Change

 

Rolling 12M YoY % = DIVIDE( [Rolling 12M Sales] - [Rolling 12M Sales YoY], [Rolling 12M Sales YoY] )

This gives a clean rolling 12 month value and a year over year comparison using the same window.

[Selva-Salimi · Accepted solution]

Hi @Yuziu8o ,

you can write measures as follow:

rolling_month := calculate(sum(sales), filter (all(table) , date >= EDATE (max(date) , -12)))

YOY := divide ( calculate(sum(sales), filter (all(table) , date >= EDATE(EDATE (max(date) , -12),-12) && date < EDATE(max(date), -12))) , calculate(sum(sales), filter (table , date >= EDATE (max(date) , -12))))

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.

[Jihwan_Kim]

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.

Sales total: = 
SUM( sales[sales] )

12 Months rolling: = 
VAR _lastdate =
    MAX ( sales[date] )
VAR _t =
    SUMMARIZE (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year-Month sort] <= EOMONTH ( _lastdate, 0 )
        ),
        'Calendar'[Year-Month],
        'Calendar'[Year-Month sort]
    )
VAR _window =
    WINDOW ( 1, ABS, 12, ABS, _t, ORDERBY ( 'Calendar'[Year-Month sort], DESC ) )
RETURN
    IF ( COUNTROWS ( _window ) >= 12, CALCULATE ( [Sales total:], _window ) )

12 months rolling YoY %: = 
VAR _currentrolling = [12 Months rolling:]
VAR _prevyearrolling =
    CALCULATE (
        [12 Months rolling:],
        OFFSET (
            -12,
            ALL ( 'Calendar'[Year-Month], 'Calendar'[Year-Month sort] ),
            ORDERBY ( 'Calendar'[Year-Month sort], ASC )
        )
    )
RETURN
    IF (
        _currentrolling,
        DIVIDE ( _currentrolling - _prevyearrolling, _prevyearrolling )
    )