Filter Previous Data DAX on condition

[Anonymous]

Imagine I have the following dataset.

Product / IsOpen / Date
1           / 0          / 21 / 05 / 2009

1         / 1           / 22 / 05 / 2009

How can I filter/delete data previously to the first IsOpen = 1 ?? By creating a new table or even in Powerquery. I just want data starting from its first IsOpen = 1 per Product.

Thanks!

[fhill]

... is that first IsOpen for ALL products, or by Product?  What about Products that close after opening?  What is your desired output based on these values?

Product	IsOpen	Date
A	0	1/2/2012 0:00
A	0	1/5/2012 0:00
A	1	1/7/2012 0:00
B	1	3/1/2012 0:00
B	1	3/5/2012 0:00
B	1	3/7/2012 0:00
C	0	6/2/2012 0:00
C	1	6/4/2012 0:00
C	1	6/6/2012 0:00
D	0	12/20/2011 0:00
D	1	12/23/2011 0:00
D	0	12/31/2012 0:00

[Anonymous]

by product.
So in your dataset, this would happen:

Product A completely gone
Product B every row
PRoduct C the last two.. since the first one is IsOpen = 0 D the last two as well..

Products that close after opening would appear.. as long as opening is first, then everything appears.. everything that appears as closed before the first isOpen = 1 should be gone.
I think I might found the solution, will let you know if it works

[fhill · Accepted solution]

I've done this with 2 calcualted columns...  (You could combine them in 1 if you wanted).

VisCheck looks for First Date IsOpen = 1 comparing each Produt Line to the EARLIER Product line.  This allows for different dates depending on Product.  Then Yes-No compares the dates placing 0 or 1.  I then Filter by only 1.

** I noticed you said "Product A Completely Gone", but there was 1 IsOpen with a date?  Mabye you can play with the > or >= sign in the VisCheck to acomplish what you need.

VisCheck = CALCULATE(FIRSTDATE(Prod[Date]), Prod[IsOpen] = "1" ,FILTER( Prod, Prod[Product] = EARLIER(Prod[Product])))

Yes-NO = IF(Prod[Date] < Prod[VisCheck],0,1)

Thank You,

FOrrest

[Anonymous]

This is a good solution, in my case the time is also important.
Anyway, decided to do via power query and it works. 
Thanks!