Days Back Filter (1,7,28)

[Marshy]

I have a table that looks like this: 

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

[amitchandak]

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

[Mariusz · Accepted solution]

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