Forum Discussion

mb769's avatar
mb769
Icon for Advocate I rankAdvocate I
6 months ago
Solved

Field parameter for type of date selection

Hi Community,   In my report, I want to give the user the possibility to select the time period for which the data is shown. I want this in different levels: Calendar year (e.g. 2024, 2025, 2026...
Need Help
  • mb769's avatar
    mb769
    6 months ago

    Success, based on this Youtube video:
    https://www.youtube.com/watch?v=fKygF7VEJnQ

    I changed the 'date period' table to Field parameters, and added a dropdown list with the field parameters value which is only visible when the 'Date slicer Filter' = 0

JamieHolding's avatar
JamieHolding
Icon for Resolver I rankResolver I
6 months ago

I would treat each of them in isolation. Year is a number, Academic Year and Month (with year) are ordered text fields. You're right that it needs to have a date to do the relative selection.

So you could add a dropdown slicer for the first 3, then a between slicer for the last one. 

mb769's avatar
mb769
Icon for Advocate I rankAdvocate I
6 months ago

Yes, but when using different slicer, how to make suren that they do not interfere with one another?
E.g. when using the "Academic year" slicer, my visuals should not respond to whichever date is selected in the between slicer.

  • JamieHolding's avatar
    JamieHolding
    Icon for Resolver I rankResolver I
    6 months ago

    What would be the between slicer in that scenario?

    Is this not yet an 'Edit Interactions' issue and you don't want slicers to affect other slicers?

    • mb769's avatar
      mb769
      Icon for Advocate I rankAdvocate I
      6 months ago

      Well, I'll give a concrete example.
      This visual gives the number of staff members who are on sick leave. (There's also a card visual, which counts the total number of people with sick leave during that period. The use case is not limited to this specific scenario) 

      As you can see, this is shown for the Academic Year 2024-2025, which is selected by a slicer on the report page.

       

      What I want now, is that the user can choose whether he/she sees the visual (and all corresponiding statistics) for EITHER an Academic Year, OR a Calendar Year OR a custom period (i.e. the betwee slicer).

      If I put the 3 different slicers on the report page, all slicers work simultaneously, which is not what I want.

      Therefore, I was thinking about a field parameter, where you can choose in one slicer whether you want an acedemic year / calendar year / custom period, and in another slicer you choose WHICH period (the different possible values of calendar year or academic year).
      This is where I'm stuck: in this scenario, I cannot show a user-friendly between-slicer when "Custom period" is selected.

      • JamieHolding's avatar
        JamieHolding
        Icon for Resolver I rankResolver I
        6 months ago

        I see. To set this up I think you need a parameter with the fields from your date table. Replace the static field in your chart with the field parameter - use a dropdown to navigate between hierarchies.

        If your data looks off, you may need to reconfigure measures for SELECTEDVALUE() something like

        Count by Date Hierarchy = 
SWITCH(
    SELECTEDVALUE('Date Hierarchy Selector'[Parameter], "CalendarYear"),
    "CalendarYear", [Total by Calendar Year],
    "FinancialYear", [Total by Financial Year],
    "Year Quarter", [Total by Quarter],
    BLANK()]
)


        Alternatively it may be easier to just make 3 identical charts placed over each other, set different dates to be the x axis for each chart, then use bookmarks to navigate between the different date types. Personally I would do this as it's a bit easier to manage.