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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
H_Jones
Helper I
Helper I

Overlapping Slicer

Hello PBI Community...

 

I have a question that I don’t even know is possible.

 

I have a report in PBI desktop and I want there to be buttons (potentially a slicer) to show the income either for last week or the year to date (YTD). Clearly last week is a subset of YTD so the two options in the slicer are not unique... is this possible or can someone come up with a work around, I’m reasonably new to PBI so don’t know all the tricks of the trade yet.

 

My initial work around was to have a slicer with 'last week' and 'YTD excluding last week', and people would have to use 'select all' to see the full YTD. But as I build these reports for other teams, I noticed that the team were using it incorrectly as it’s not very intuitive (they were just selecting the YTD minus last week as the full YTD).

 

Please help,

Thanks.

 

Harriet.

 

1 ACCEPTED SOLUTION
Beckham
Advocate II
Advocate II

I'd do a disconnected table/switch combination that looks like this:

 

Step #1 Create a disconnected table:

Number    Chooser   

0               YTD

1               Last Week

 

Step 2 the measure

= Switch(min(DisconnectedTable[Number]),

0,[Calculation #1],

1,[Calculation #2])

 

Step 3. Bring the Chooser column, from the disconnected table, on to the pivot as a slicer. When you select YTD, the min will filter to 0, and the switch will run calc #1. When you select Last Week, the min will filter to 1, and the switch will run calc#2

 

Essentially you make two distinct calcs, one that does YTD, and another that does last week. Then the switch will toggle between them. 

 

Does this help?

View solution in original post

4 REPLIES 4
v-sihou-msft
Microsoft Employee
Microsoft Employee

@H_Jones

 

In this scenario, you can first create two measures for YTD and Last Week. Then create another new table for these two measures. So that either of the measures can be selected in the slicer and you can get the expected result.

 

Please refer to following steps:

  1. Create two measures for YTD and Last Week. I assume week begins on Sunday (1) and ends on Saturday (7) here.
    YTD_Income = 
    CALCULATE ( SUM ( Table1[Income] ), DATESYTD ( Table1[Date] ) )
    
    LastWeek_Income = 
    VAR LastDay = LASTDATE ( Table1[Date] )
    VAR WeekDayNum = WEEKDAY ( LastDay )
    RETURN
        (
            CALCULATE (
                SUM ( Table1[Income] ),
                FILTER (
                    ALL ( Table1[Date] ),
                    Table1[Date] > LastDay - 7 - WeekDayNum
                        && Table1[Date] <= LastDay - WeekDayNum
                )
            )
    )
    
  2. Create a new table for above two measures.
    369.png
  3. Drag a slicer into your canvas and put “Measure” column into Field.
    69.png

Regards,

Beckham
Advocate II
Advocate II

I'd do a disconnected table/switch combination that looks like this:

 

Step #1 Create a disconnected table:

Number    Chooser   

0               YTD

1               Last Week

 

Step 2 the measure

= Switch(min(DisconnectedTable[Number]),

0,[Calculation #1],

1,[Calculation #2])

 

Step 3. Bring the Chooser column, from the disconnected table, on to the pivot as a slicer. When you select YTD, the min will filter to 0, and the switch will run calc #1. When you select Last Week, the min will filter to 1, and the switch will run calc#2

 

Essentially you make two distinct calcs, one that does YTD, and another that does last week. Then the switch will toggle between them. 

 

Does this help?

Thanks!

 

Harriet.

samdthompson
Memorable Member
Memorable Member

Hi. make a calculated column with either current week or YTD. something like:

 

if(now()>week_start_date, = "current_week",if(now()-365>week_start_date,"YTD","other")).

 

use that in your slicer and you will get either YTD or last_wk or other

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.