How do I flag every instance?

JayJay0368 · ‎06-14-2023

Hi people,

I need you help with the following:

I have a Table in Power BI which shows the following:

now, I want to make a column, which calculates the following logic:

When there has been a visit ("Date of visit (flag)"=1) in a store (in this example "Store 1"), I need to flag the first date AFTER the visit where the Product (in this example "Product 1") is not present ("Product is not present"=1).

I have tried to make the following DAX calculation (Calculated column):

First Instance of "product not present" after "visit" (flag) =

IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table[customer] = EARLIER('Table'[customer]) &&
            'Table'[Product] = EARLIER('Table'[Product]) &&
            'Table'[Date] <= EARLIER('Table'[Date])&&
            'Table'[Date of Visit (flag)]=1 
        )
    ) >= 1,
    IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table[customer] = EARLIER('Table'[customer]) &&
            'Table'[Product] = EARLIER('Table'[Product]) &&
            'Table'[Date] <= EARLIER('Table'[Date])&&
            'Table'[Product not present (flag)]=1 
        )
    ) = 1,1,0))

RESULT:

which is close. But I need a "flag" for EVERY time there has been a visit and the product is not present afterwards. I the calculation I have made it only shows the first time.

What I need is the above example is this result:

one more visit is taking place on the "09-05-2023" and the product is (still) not present, so I need another flag on the date after the visit date, "14-05-2023". And everytime the combination of "visit date" and "product not present" turns up.

I have added a link to a spreadsheet with the table used as an example:

example - table

Any help or guidance is REALLY appreciated. Thanks.

Br,

Jayjay0306