Help with Custom date slicer values

[srk_powerbi]

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

[v-eqin-msft · Accepted solution]

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

[srk_powerbi]

@Greg_Deckler  can you help please?

[richbenmintz]

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!

[AlB]

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]

@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?