dynamically show last x months - correct dax formula

[kaileena]

Hello!

I think i am very close to what I want. But I think my dax is incorrect.

When i select a date from the slicer, i want to see the last 12 month wrt slicer selection. 

Example: slicer selection january 2021 => the graph shows from february 2020 to january 2021.

I know the question was asked before. I tried to customise my dax accoordingly. But I have a question:

I have a lot of graphs that are time relative so x axis is 'Date' on all of them. This 'Date' comes from 'calendar' table which is used also for slicer. If i create a new time table for the slicer, independent from the other tables,  how do i ensure connectivity with the other grahs?

thank you for your kindness

dax

Minus12Month =

CALCULATE (

SUM('Measurement'[Value]),

'Measurement'[Type] IN { "Budget Planned" },

FILTER (

ALL ( 'Calendar'[Date] ),

[Date] > EOMONTH ( MAX ( [Date] ), -12 )

&& [Date] <= EOMONTH ( MAX ( [Date] ), 0 )

)

)

[v-jingzhang · Accepted solution]

Hi @kaileena 

Your DAX is to calculate the cumulative total in the last 12 months previous of the selected month rather than to show the values of the last 12 months. You need to have a new date table for the slicer and keep it independent from other tables. Then create a measure to compare the selected value from the slicer to the dates in other visuals. Put date column from calendar table into other visuals. 

Date Filter = 
VAR maxDate = MAX('Period Slicer'[Date])
VAR minDate = EOMONTH(MAX('Period Slicer'[Date]),-12)
RETURN
IF(MAX('Date'[Date])>minDate && MAX('Date'[Date])<=maxDate, 1, 0)

Then add this measure on the visual as a filter. Set it to show items when value is 1. 

I have attached a sample pbix at bottom. Hope it helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.