cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Show last 3 months in table when filter applied

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.

1 ACCEPTED SOLUTION
Super User

@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:

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) )

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

4 REPLIES 4
Super User

@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:

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) )

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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

Super User

@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...

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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!