Custom Date Filter

[Alex-PBIComm]

Good morning,

I'm trying to create a custom filter based on a date column.  The filter should be used as slicer and contain only 3 values: "Today", "Yesterday", "Last Week".
I tried with a column but the problem there is that if the record is "Yesterday", it can't be at the same time "Last Week", therefore for "Last Week" I'm counting last 7 days minus today and yesterday. So I'm supposing this can be solved with a measure?

Thanks in advance for your help.

AB

[amitchandak]

@Alex-PBIComm , Actually Yesterday and Last Week, is not mutually exclusive. So create three measures to filter these and then use measure slicer

Today = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=Today())

yesterday = CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]=Today() -1 )

Last Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1 -7
var _end =today()+ 7-1*WEEKDAY(today(),2) -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter

measure slicer
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://www.youtube.com/watch?v=vlnx7QUVYME

[Alex-PBIComm]

Good afternoon, thanks for your reply, but what if the slicer should only filter a table without returning any calculation?
I've read the linked article, and it's quite different from what I'm looking for. 
What I need is a slicer that contains "Today", "Yesterday" and "Last Week", based on a date column, and that once one of the 3 is selected the whole related table will be filtered accordingly, and that selection will be reflected in all the visualizations present in the sheet. 

[Anonymous · Accepted solution]

Hi @Alex-PBIComm ,

First create a category table, and then create a measure.

Filtered Value =
SWITCH (
    SELECTEDVALUE ( 'Table'[Category] ),
    "Today",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER ( 'Table (2)', [Date] = TODAY () )
        ),
    "Yesterday",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER ( 'Table (2)', [Date] = TODAY () - 1 )
        ),
    "Last Week",
        CALCULATE (
            SUM ( 'Table (2)'[Value] ),
            FILTER ( 'Table (2)', [Date] >= TODAY () - 6 && [Date] <= TODAY () - 3 )
        )
)

If you want the reflection of all visualizations by filtering of the custom date slicer, you need to replace the values in the original visualization with the above measure or similar measures.

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.