Show last 3 months in table when filter applied

[shyammayhs]

Hi guys,

I would like a table to show the past 3 months (Year Month) when I select a Year and a Month in the filters. How do I achieve this? I have done some googling but nothing seems to be helping me.

I have tried to use this measure/expression = calculate(expression, filter(Date >= StartPeriod && Date <= EndPeriod))

Thanks.

[AllisonKennedy · Accepted solution]

@shyammayhs  Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

If you want to compare Month on Month you could either use Relative Date slicer:

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range 

OR create DAX measures: 

Sales = SUM(FactTable[SalesValue])

Sales -1 Month = CALCULATE( [Sales], DATEADD(DimDate[Date], -1, MONTH) )

Sales -2 Month = CALCULATE( [Sales], DATEADD(DimDate[Date], -2, MONTH) )

etc.

OR create DAX for last three months and put Month (from your date table) in Axis of visual: 

Sales Last Three Months = 
VAR _StartDate = MAX(DimDate[Date])

CALCULATE( [Sales], DATESINPERIOD(DimDate[Date], _StartDate, -4, MONTH) )

[shyammayhs]

@AllisonKennedyHi, thanks for your response.
I do have a DimDate table in my data model. Which has a relationship to my Fact table.

I also have tried using DATESINPERIOD (should all the Date values in here be from the DimDate table?), which is showing me the correct value for the last 3 months, but when I select a previous month in the filter, it only shows the Measure value for the month that it is filtered down to. Whereas I would still like to see the Filter Month - 3 months.

Thanks

[AllisonKennedy]

@shyammayhs  What's your exact DAX please you've tried. 

Yes- all dates in DATESINPERIOD should come from DimDate table, but the StartDate needs to be using the filter you have applied, which my formula should do for you...

[shyammayhs]

@AllisonKennedyThanks, I have solved the issue now!
I do have this new issue though, not sure if you've encountered something like this before?

https://community.powerbi.com/t5/Desktop/Loyal-Vs-Risk-Customers/m-p/2137816#M789182

Thanks!