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 a...
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

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!