cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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

1 ACCEPTED SOLUTION
Community Support

Hi @srk_powerbi ,

1. Create a single table for Slicer like this:

2. Use the following formula after

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

Best Regards,
Eyelyn Qin

5 REPLIES 5
Community Support

Hi @srk_powerbi ,

1. Create a single table for Slicer like this:

2. Use the following formula after

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

Best Regards,
Eyelyn Qin

Helper II

Solution Sage

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 my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!

Super User

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

Helper II

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.