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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Marshy
Frequent Visitor

Days Back Filter (1,7,28)

I have a table that looks like this: 

Marshy_0-1593679903685.png

I want to be able to make a custom filter or slicer that that has 3 options(1,7,28):
if 1 then calculate column ship_visit_1d

if 7 then calculate column ship_visit_7d
if 28 then calculate column ship_visit_28d

 

I can't for the life of me figure this out, can anyone help?

TIA

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

Hi @Marshy 

 

 

1. Create a table with your tree rows 1, 7 and 28.

2 Create Measure:

 

result = 
SWITCH(
    SELECTEDVALUE( SelectionTable[selection] ),
    1, SUM( table[ship_visit_1d] ),
    7, SUM( table[ship_visit_7d] ),
    28, SUM( table[ship_visit_28d] )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Marshy , refer if this can help

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

 

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,today()-7,7,day))
Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table,today()-14,7,day))

 

7 Days behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAY))
14 Days behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAY))
21 Days behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAY))
Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Mariusz
Community Champion
Community Champion

Hi @Marshy 

 

 

1. Create a table with your tree rows 1, 7 and 28.

2 Create Measure:

 

result = 
SWITCH(
    SELECTEDVALUE( SelectionTable[selection] ),
    1, SUM( table[ship_visit_1d] ),
    7, SUM( table[ship_visit_7d] ),
    28, SUM( table[ship_visit_28d] )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

 

Helpful resources

Announcements
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!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!