Create filter using DIM_Date Table

[Anonymous]

Hi Experts

No sure if this is pretty striaght forward or more complex then i am making it out to be. I have a Dim-Date Table where the first column is the date column which cover a period (todays back to the last 5yrs).

I want to be able to create a slicer based on the date column where i can filter my data based on the last 7 Days, 30 Days and 60 days, using a slicer.

note the last 30 days must incl the last 7 days values too....

[amitchandak]

Refer, how to create a slicer to choose a measure

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Formula example

Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-7,Day))  
Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-14,Day))
Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))
Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-60,Day))

Appreciate your Kudos.

[Anonymous]

HI Amit

thanks for the feedback but i am getting the following error message

Error Message:

MdxScript(Model) (63, 73) Calculation error in measure '_Measures'[Rolling 7]: An invalid numeric representation of a date value was encountered.

[dax · Accepted solution]

Hi @Anonymous , 

As I know, you could use slicer->relative date , which you could choose last N day without creating "Result column". In addition, you also could create last 7, last 30 's measure and use it when you choose this in slicer, you could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Anonymous]

Simply use relative date slicer and filter out data as you want.

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

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.