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

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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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.