Rolling 12 month per month in a montly report

[Anonymous]

Hi,

I have a montly report where all data is filtered by a Slicer (e.g. 2024-12).

I have a measure that calculate Rolling 12M sales which for 2024-12 summarize Total sales for 2024-01 to 2024-12.

In this montly report I want to have a line chart that shows Rolling 12M per months the last 12 months which in above example show X-axis from 2024-01 to 2024-12 where 2024-01 summarizes sales from 2023-02 to 2024-01 (Rolling 12 at 2024-01)

Today, I try to use below DAX but this will only give me the value of 2024-12

Last12MonthsSales =

CALCULATE(

    [Total Sales],

    DATESINPERIOD('Date'[Date],max('Date'[Date]),-12,MONTH)

)

Anyone that could support?

[anilelmastasi · Accepted solution]

Hello @Anonymous ,

Could you try this please?

Last12MonthsSales =
VAR CurrentDate = MAX('Date'[Date]) -- Get the current date in the context of the chart
RETURN
CALCULATE(
[Total Sales],
DATESINPERIOD('Date'[Date], CurrentDate, -12, MONTH) -- Rolling 12 months from each point on the X-axis
)

[Fowmy · Accepted solution]

@Anonymous 

It seems you need to be able to select a particular month and a chart to show past number of months, 12 in your case, check this video out: https://youtu.be/d8Rm7dwM6gc

[anilelmastasi · Accepted solution]

Hello @Anonymous ,

Could you try this please?

Last12MonthsSales =
VAR CurrentDate = MAX('Date'[Date]) -- Get the current date in the context of the chart
RETURN
CALCULATE(
[Total Sales],
DATESINPERIOD('Date'[Date], CurrentDate, -12, MONTH) -- Rolling 12 months from each point on the X-axis
)