Forum Discussion

SachinC's avatar
SachinC
Helper V
4 years ago
Solved

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 defaulte...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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



Anonymous's avatar
Anonymous
Not applicable
4 years ago

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