Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Control Range Date by Dropdown filter

Hi all,   I have a table calendar with data in each hour. I created a slicer for date between. I want to create a dropdown list which calculate today, last 7 days,... and when I choose one value,...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    A workaround below:

    Create a date table

    date = CALENDAR(MIN('Table'[date]),TODAY())

    add a column

    slicer item =
SWITCH (
    TRUE (),
    [Date]
        = TODAY (), "today",
    DATEDIFF (
        [Date],
        TODAY (),
        DAY
    ) <= 7
        && [Date]
            < TODAY (), "last 7 days",
    DATEDIFF (
        [Date],
        TODAY (),
        DAY
    ) <= 30
        && [Date]
            < TODAY (), "last 30 days"
)

    Then edit interactions as below

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    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
4 years ago

Hello,

i have tried your solution, but I came across an issue. When you use this formula, you will have something like this:

 

10/11/2021 = today

10/10/2021 = yesterday

10/09/2021 = last 7 days

10/08/2021 = last 7 days

10/07/2021 = last 7 days

10/06/2021 = last 7 days

10/05/2021 = last 7 days

10/04/2021 = last 30 days

....

 

and then, when you filter last 7 days, you only get data for 10/09-10/4 instead of 10/11-10/4. Same for last 30 days. Do you get my point?

 

I even tried to build different columns like "last week", "last month", "last year", but then I have problem with hierarchies.  Anyone has solution for that?

 

Thank you.

 

Best Jan