Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post 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 in the report that contains both past and future days - existing relative date slicer is either one or the other.

 

So I added the 2 parameters, and used them with a calculated column to check if every order production date would fall into the interval.

 

It works for the first parameter setting however it does not refresh when I change the parameters further. It's the first time I use what-if parameters so maybe this is normal? If it is, what am I supposed to do to have a configurable relative date?

 

Thanks!

Kind regards

Valeria

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

11 Replies

  • johnt75's avatar
    johnt75
    Super 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

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    4 years ago

    Hello, thanks, I have tried this soltuion, however I cannot make it work. The expression is giving me a red rectangle in the editor at the end, but without telling me what's wrong - I did check with cards that the startDate/endDate were calculating correctly and this is the case, so the issue must be with the final formula  

    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN ( 'Date'[Date], startDate, endDate )
    )

     I tried to put this a a filter in the visual and applying to when it is not blank - but result is a blank visual.

    Sorry but I don't know much about SELECTEDMEASURE() and how to debug this...

    Thanks!

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

      Have you just put that into a measure in Power BI desktop? You need to create a calculation group and calculation item in Tabular Editor.

      • ValeriaBreve's avatar
        ValeriaBreve
        Post Partisan
        4 years ago

        Hello, sorry! I don't have the habit to do this, I really need to learn. So I did add the calculation group/item in tabular editor and then added this as a filter to my visual, but it is not filtering anything when selecting the calculation item...

        Can you understand from my screenshots what I am not doing correctly?

        Thanks!

        Kind regards

        Valeria