Forum Discussion
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
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216- jayjay0306
Helper III
Hi Ibendin,
off course. I have now added an excel file with comments in this link:
https://1drv.ms/x/s!Aq3P3n08LNKnhKVlRKvUZm9ZPJZ0bQ?e=Za4LW9
If it is still not clear what I am trying to accomplish, please let me know.
I appreciate the help.
thanks.
Br,
JayJay0306