Forum Discussion

srk_powerbi's avatar
srk_powerbi
Helper II
5 years ago
Solved

Help with Custom date slicer values

Hi,

I have a requirement to create slicer with custome date values. 

The values are  

All

Current Quarter

Last Quarter

Last 2 Quarters

 

So, the visuals on the report needs to show the data as per above date slicer value selection.

Ex: if i select 'Last Quarter'  report need to show data for last quarter only.

I have a Date table and Fact table with date columns and relationship between them.

Please can help how can i achieve this.

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi srk_powerbi ,

    You could follow these steps:

    1. Create a single table for Slicer like this:


    2. Use the following formula after     adding a Quarter column:

     

    Measure =
VAR _sele =
    SELECTEDVALUE ( forSlicer[Type] )
RETURN
    SWITCH (
        _sele,
        "ALL", 1,
        "Current Quarter", IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ), 1, 0 ),
        "Last 2 Quarters",
            IF ( QUARTER ( TODAY () ) - MAX ( 'Date'[Quarter] ) >= 1, 1, 0 ),
        "Last Quarter",
            IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ) - 1, 1, 0 )
    )

     

     

     3. Apply the measure to filter pane (set as "=1") . My final output looks like this:

     

    Please take a look at the pbix file here.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

    Best Regards,
    Eyelyn Qin

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi srk_powerbi ,

    You could follow these steps:

    1. Create a single table for Slicer like this:


    2. Use the following formula after     adding a Quarter column:

     

    Measure =
VAR _sele =
    SELECTEDVALUE ( forSlicer[Type] )
RETURN
    SWITCH (
        _sele,
        "ALL", 1,
        "Current Quarter", IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ), 1, 0 ),
        "Last 2 Quarters",
            IF ( QUARTER ( TODAY () ) - MAX ( 'Date'[Quarter] ) >= 1, 1, 0 ),
        "Last Quarter",
            IF ( MAX ( 'Date'[Quarter] ) = QUARTER ( TODAY () ) - 1, 1, 0 )
    )

     

     

     3. Apply the measure to filter pane (set as "=1") . My final output looks like this:

     

    Please take a look at the pbix file here.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

    Best Regards,
    Eyelyn Qin

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi srk_powerbi 

    You can add a column to your date table classifying the quarters:

    Current  2020Q4
    Last 2020Q3
    Last 2 2020Q3
    Last 2

    2020Q2
    Other

    All the other quarters

    and use that column as slicer. The all option would just be no selection on the slicer

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

     

    • srk_powerbi's avatar
      srk_powerbi
      Helper II
      5 years ago

      AlB  thanks for replying.

      But, how do we add two values 

       

      lest say for  2020Q4  it comes under 'Current Quarter' and 'Last 2 Quarters' ? we can assign one value to one row in calender table right?

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      5 years ago

      Hi srk_powerbi,

       

      you can use the disconnected dimension pattern for this.

       

      create a table that contains the static values

      Current Quarter, Last Quarter, Last Two Quarter.

      Then you would create a measure that looked something like

      disconnected value = if hasonevalue('table'[label]),
switch(values('table'[label]),
"Current Quarter", calculate(sum(table[something]), filter(dates, dates[lastquarter] = true())),
......
)

      you will need a date table which includes columns that identify if the date ranges are true or false. If you want to allow the user to define the end date then you would use the selected date and time intelligence functions to find the appropriate period.

       

      Hope this Helps,
      Richard
      Did I answer your question? Mark my post as a solution!
      Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!