Using date Slicer to find what state the product is in the selected date.

[Dhacd]

Good Day Team,

Let's get familiarized with the dataset.

Here is the product status table. (Please read the date header as LoggedDate) 

Along with this we also have a master calendar table which is just a calendar table date starting from June 1 to June 30th

The problem,
We are bringing all the columns of the product status table to a table to the front end and the date column(from the master calendar) as a slicer to the front end.
Now when we select a date in the slicer it should show whether any products are in the system at any status.
For eg:
Suppose we select the 4th of June.

The table should only show the below records(Since these 2 products are the only records before the 4th-June and have a record after the 4th-June).

Index	Product	LoggedDate	State
1	Apple	03-Jun	Dispatched
4	Orange	03-Jun	Ordered

The sample data is attached below.

Index	Product	LoggedDate	State
0	Apple	01-Jun	Ordered
1	Apple	03-Jun	Dispatched
2	Apple	05-Jun	On Transit
3	Apple	06-Jun	Delivered
4	Orange	03-Jun	Ordered
5	Orange	05-Jun	Dispatched
6	Orange	07-Jun	On Transit
7	Orange	08-Jun	Delivered
8	Grapes	05-Jun	Ordered
9	Grapes	07-Jun	Dispatched
10	Grapes	09-Jun	On Transit
11	Grapes	10-Jun	Delivered
12	Mango	07-Jun	Ordered
13	Mango	09-Jun	Dispatched
14	Mango	11-Jun	On Transit
15	Mango	12-Jun	Delivered

Break a leg, guys,
Thanks in advance.
and regards,
Atma.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I suggest having a disconnected calendar table like below.

LoggedDate measure: = 
VAR _lastlogdate =
    CALCULATE (
        LASTNONBLANK ( Data[LoggedDate], MAX ( Data[LoggedDate] ) ),
        FILTER (
            ALL ( Data ),
            Data[Product] = MAX ( Data[Product] )
                && Data[LoggedDate] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Data[Index] ),
        CALCULATE (
            MAX ( Data[LoggedDate] ),
            KEEPFILTERS ( Data[LoggedDate] = _lastlogdate )
        )
    )

State measure: = 
VAR _lastlogdate =
    CALCULATE (
        LASTNONBLANK ( Data[LoggedDate], MAX ( Data[LoggedDate] ) ),
        FILTER (
            ALL ( Data ),
            Data[Product] = MAX ( Data[Product] )
                && Data[LoggedDate] <= MAX ( 'Calendar'[Date] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Data[Index] ),
        CALCULATE (
            MAX ( Data[State] ),
            KEEPFILTERS ( Data[LoggedDate] = _lastlogdate )
    )
)