Relative Date

[SachinC]

Hi,

I have a database field with a simple date i.e. 09/09/2011 11:13:13.

I want to create a DB column so that I can use it as a filter in my visual, i.e. a combo.

The filter combo will be defaulted to Last 120 days, but I want to toggle it to Last 30, Last 60, Last 90, Last 120 and finally >120 days.

How do I get this done(?)  I envisage a formula where I am looking at the date vs CurrentDate() and put a label on it, i.e. Last 30 days. But I am lost!  Need help!

Thanks!

[AntoineTRICHET · Accepted solution]

Hello @SachinC 

You can manually enter a table in Power BI with only one column as shown below, containing all the options you want.

Then you put this field in your slicer (no need to connect this new table to your data model).
And finally add a test in all the formula you are using in your visual :
YourNewMeasure =
IF(SELECTEDVALUE(Table[TableForDatePeriod])="last 30",CALCULATE(YourMeasure,FILTER(YourTable, YourTable[Date]>=DATEADD(TODAY(),-30,day)),
IF(..................),
IFSELECTEDVALUE(Table[TableForDatePeriod])="last 120",CALCULATE(YourMeasure,FILTER(YourTable, YourTable[Date]>=DATEADD(TODAY(),-120,day)),
IF(SELECTEDVALUE(Table[TableForDatePeriod])=">120",CALCULATE(YourMeasure,FILTER(YourTable, YourTable[Date]<DATEADD(TODAY(),-120,day)))))

Please accept it as solution if it solved your issue