Slicer with an OR argument

[LinaP_]

Hi all, 

In a report I have visualized "Planned delivery dates" and "Actual delivery dates" in a table with the order number (uniqe).

I have a mock version of the table here:

Order number	Planned date (YYMMDD)	Actual date (YYMMDD)	Difference in days
A1	2022-01-01	2022-02-01	30
B2	2022-02-01	2022-01-15	-15
C3	2022-03-01	2022-04-15	45
D4	2022-04-01	2022-01-01	-90
E5	2022-05-01	2021-05-01	-365

I have added a slicer with relative date (ex. last 6 months) and have played around with Planned dates and Actual dates.

The problem is I always miss some data as the dates can differ quite significantly in time (+100 days in some cases). 

In my head the solution is quite simple, a slicer with Planned date OR Actual date occurring in the last X months. Anyone know if this is possible?  

[LinaP_]

Hi @v-yiruan-msft 

Thanks for the help! It captures all orders now that has an end date, which is more than I could solve! 😄

One issue still remains, which I did not include in my example, is that some of the "Actual" dates haven't happened yet:

Order number	Planned (YYMMDD)	Actual (YYMMDD)	Difference in days
A1	2023-01-01	2023-01-01	0
B1	2023-01-01	2023-02-01	30
C1	2023-05-01	2023-04-15	-15
D1	2023-05-01		25

Do you have any idea on how to include that?

Warm Regards

[v-yiruan-msft · Accepted solution]

Hi @LinaP_ ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table and apply it to the slicer

Note: Please DO NOT create any relationship with the fact table

2. Create a measure as below

Flag = 
VAR _selpdate =
    SELECTEDVALUE ( 'Table'[Planned date (YYMMDD)] )
VAR _seladate =
    SELECTEDVALUE ( 'Table'[Actual date (YYMMDD)] )
RETURN
    IF (
        (
            _seladate >= MIN ( 'Calendar'[Date] )
                && _seladate <= MAX ( 'Calendar'[Date] )
        )
            || (
                _seladate >= MIN ( 'Calendar'[Date] )
                    && _seladate <= MAX ( 'Calendar'[Date] )
            ),
        1,
        0
    )

3. Apply a visual-level filter on the visual with the condition(Flag is 1)

Best Regards

[LinaP_]

Hi, 

Really appreciate the help! 

Needed to do a modification to work with blank dates as following: but now it works like a charm. 

Flag = 
VAR _selpdate =
    SELECTEDVALUE ( 'Table'[Planned date] )
VAR _seladate =
    IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Actual date] ) ), TODAY(), SELECTEDVALUE ( ''Table'[Actual date] ) )
RETURN
    IF (
        (
            _selpdate <= TODAY() &&
            (
                (
                    _selpdate >= MIN ( 'Calendar_for_measurment'[Date] )
                    && _selpdate <= MAX ( 'Calendar_for_measurment'[Date] )
                )
                ||
                (
                    _seladate >= MIN ( 'Calendar_for_measurment'[Date] )
                    && _seladate <= MAX ( 'Calendar_for_measurment'[Date] )
                )
            )
        ),
        1,
        0
    )