Forum Discussion

JayJay0368's avatar
JayJay0368
Frequent Visitor
3 years ago

Calculated column showing product not present

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',
            [Customer] = EARLIER('Table'[Customer]) &&
            [Product] = EARLIER('Table'[Product]) &&
            [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. 

 

 

Any help or guidance is much appreciated. Thanks.

 

Br,

Jayjay0306 

2 Replies