cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SachinC
Helper V
Helper V

Relative Date

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!

1 ACCEPTED SOLUTION
AntoineTRICHET
Resolver III
Resolver III

Hello @SachinC 

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

AntoineTRICHET_0-1631178159625.png

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



View solution in original post

1 REPLY 1
AntoineTRICHET
Resolver III
Resolver III

Hello @SachinC 

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

AntoineTRICHET_0-1631178159625.png

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



View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.