Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
edant
Regular Visitor

Slicer options return incorrect data

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.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1639101024178.png

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.

vyalanwumsft_1-1639101213611.png

The final output is shown below:

vyalanwumsft_2-1639101238612.pngvyalanwumsft_3-1639101249635.png

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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1639101024178.png

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.

vyalanwumsft_1-1639101213611.png

The final output is shown below:

vyalanwumsft_2-1639101238612.pngvyalanwumsft_3-1639101249635.png

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.

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

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.