Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Use What-If parameter for selecting a date interval

Hi, I wanted to use the what-if parameters for users to be able to select: 1) Number of days before production date 2) Number of Days after production date   So as to give them a relative date i...
Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    A calculated column won't work as it is only calculated during data refresh, it does not take into account any filters or slicers so won't update when your user changes the what if parameter.

    You could create a calculation group with a single calculation item, something like

    Filtered days calculation =
VAR numDays = SELECTEDVALUE ( 'What if parameter'[Num days] )
VAR baseDate = TODAY ()
VAR startDate = baseDate - numDays
VAR endDate = baseDate + numDays
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Date'[Date], startDate, endDate )
    )

    then apply that as a filter to the visuals which you want to be filtered

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

A calculated column won't work as it is only calculated during data refresh, it does not take into account any filters or slicers so won't update when your user changes the what if parameter.

You could create a calculation group with a single calculation item, something like

Filtered days calculation =
VAR numDays = SELECTEDVALUE ( 'What if parameter'[Num days] )
VAR baseDate = TODAY ()
VAR startDate = baseDate - numDays
VAR endDate = baseDate + numDays
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Date'[Date], startDate, endDate )
    )

then apply that as a filter to the visuals which you want to be filtered