Forum Discussion
dynamically show last x months - correct dax formula
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.
Community SupportHi 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.