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.

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.