cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Community Support

Hi @Gerald-BI ,

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.

2 REPLIES 2
Community Support

Hi @Gerald-BI ,

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.

Super User IV

@Gerald-BI , 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

Another example that can help

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-3...

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

Proud to be a Super User!

Announcements