Slicer options return incorrect data

[edant]

Hello,

I want to create a slicer with several pre-defined options to select from.

The options should return data from today, the last 7 days, the last 30 days, the last 90 days.

It seems that the data from today displayed correctly, but the rest of the options are not.

Here is what I did:

slicer item = 
SWITCH (
    TRUE (),
    [Date] = TODAY (), "Today",
    DATEDIFF (
        [Date],
        TODAY (),
        DAY
    ) <= 7
        && [Date] <= TODAY (), "Last Week",
    DATEDIFF (
        [Date],
        TODAY (),
        DAY
    ) <= 30
        && [Date]
            < TODAY (), "Last Month",
    DATEDIFF (
        [Date],
        TODAY (),
        DAY
    ) <= 90
    && [Date]
            < TODAY (), "Last 3 Months"
)

 The last 7 days return with the correct data but not including today. (I want to include also today)

The last 30 + 90 return partly data which doesn't include the current month.

Can someone suggest to me what I'm doing wrong?

Thanks in advance.

[v-yalanwu-msft · Accepted solution]

Hi, @edant ;

Because the conditional statement of switch takes the first condition [Date] = TODAY (), "Today" first, if your date meets today, then it can only return a result today instead of "Last Week";
Maybe you can try the following methods:
1.enter table.

2.create a flag measure.

flag = 
SWITCH (
    MAX ( 'slicer'[slicer] ),
    "Today", IF ( MAX ( 'Table'[date] ) = TODAY (), 1 ),
    "Last Week",
        IF (
            DATEDIFF ( MAX ( 'Table'[date] ), TODAY (), DAY ) <= 7
                && MAX ( 'Table'[date] ) <= TODAY (),
            1
        ),
    "Last Month",
        IF (
            DATEDIFF ( MAX ( 'Table'[date] ), TODAY (), DAY ) <= 30
                && MAX ( 'Table'[date] ) < TODAY (),
            1
        ),
    "Last 3 Months",
        IF (
            DATEDIFF ( MAX ( 'Table'[date] ), TODAY (), DAY ) <= 90
                && MAX ( 'Table'[date] ) < TODAY (),
            1
        )
)

3.apply it into filter.

The final output is shown below:

If it does not meet your needs, It would be great if there is a sample file without any sesentive information here.
It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu

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

[edant]

Thank you for your help, it worked like a charm 🙂

[amitchandak]

@edant , there are overlapping to can not be part of same column

refer this solution

https://community.powerbi.com/t5/Desktop/Required-custom-date-Slicer-Last-7-days-last-15-days-last-3...