Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
srk_powerbi
Helper II
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
v-eqin-msft
Community Support
Community Support

Hi @srk_powerbi ,

You could follow these steps:

1. Create a single table for Slicer like this:

10.22.2.2.PNG


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:

10.22.2.3.gif

 

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

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @srk_powerbi ,

You could follow these steps:

1. Create a single table for Slicer like this:

10.22.2.2.PNG


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:

10.22.2.3.gif

 

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
Helper II
Helper II

@Greg_Deckler  can you help please?

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!



I hope this helps,
Richard

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

Proud to be a Super User!


AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.