Skip to main content
cancel
Showing results for 
Search instead 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

Reply
shyammayhs
Advocate II
Advocate II

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
AllisonKennedy
Super User
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:

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


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

View solution in original post

4 REPLIES 4
AllisonKennedy
Super User
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:

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


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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors