Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Custom Date Filter

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...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , 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/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://www.youtube.com/watch?v=vlnx7QUVYME

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    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.