Create a filter/Slicer for Last week, Last Month & Last Quarter

[Anonymous]

Hi I would like to create filter which allows me to select the last week of data or the last month or the last quarter. My Visual is laid as per the below image.

I do have a date table which has weeknumber,month number etc...

Table visual

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

You could simply select the slicer type as "Relative Date":

Or create a new table for slicer:

Then try the following formula to create a measure:

Measure = 
var _week = IF(DATEDIFF(MAX('Table'[Week End]),TODAY(),WEEK)=1,1,0)
var _month= IF(DATEDIFF(MAX('Table'[Week End]),TODAY(),MONTH)=1,1,0)
var _quarter=IF(DATEDIFF(MAX('Table'[Week End]),TODAY(),QUARTER)=1,1,0)
return SWITCH(MAX('ForSlicer'[Type]),"Last Week", _week,"Last Month",_month,"Last Quarter",_quarter)

Last, apply the measure to filter pane, set as "=1":

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is quite cool, first option is not doable for me as baseg in UK and week start/end isnt correct ISO standard, Mon-Sun. Your second option works but need Date on the visual ?

[amitchandak]

@Anonymous , as last week and last month are overlapping. You can not do that in date table.

You have to  create an independent table with these values and then using these values you have to create a measure, just like measure slicer

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

Another example that can help

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-30/m-p/1284966#M561629

Power BI Abstract Thesis: Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE

[andy808]

What is the DAX if I am looking for LAST MONTH - not MTD. Example - it is Oct.2.2023 and I need last Month (Calendar) 9.1-9.30. I do not want weeks or days so Relative slicer does not meet my requirements. 

Thanks!