Earliest date for which the count of ingredients with cost <>0, per product, is max

[Anonymous]

Hello!

I have a table with the cost of ingredients, per product, per date. I want to see (for example in a Card), what is the first/oldest date for which the product that I select has the complete costing.

Taking the example below:

- Product A: 1/1/2021
- Product B: 1/11/2019

Product	Ingredient	Cost	Date
A	x	5	1/1/2021
A	y	5	1/1/2021
A	x	2	1/1/2021
A	y		1/12/2020
B	z		1/10/2019
B	z	3	1/11/2019

I was thinking something like: the minimum date when count distinct ingredients per product (with cost <>0) = max count distinct ingredients per product (with cost <>0). I have searched everywhere and tried so many different formulas and I cannot find the answer.

Thanks a lot!!!

[Greg_Deckler]

@Anonymous Try this:

Measure = 
    VAR __Table = SUMMARIZE('Table',[Date],[Ingredient],"__Cost",SUM('Table'[Cost]))
    VAR __Table1 = ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(__Table,"__Date",[Date])),"__Count",COUNTROWS(FILTER(__Table,[Date] = [__Date] && [__Cost] = BLANK())))
RETURN
     MINX(FILTER(__Table1,[__Count] < 1),[__Date])

[Anonymous]

Thanks SO MUCH for your quick and good answer.

It works for the majority. But for some products, this appears as blank - how can I fix this?
Could it be because in some cases an ingredient is costed at blank (as in my table example) but in other cases i have the cost as 0? 

Thanks @Greg_Deckler .

[Greg_Deckler · Accepted solution]

@Anonymous If you have a cost of 0 you could do this:

Measure = 
    VAR __Table = SUMMARIZE('Table',[Date],[Ingredient],"__Cost",SUM('Table'[Cost]))
    VAR __Table1 = ADDCOLUMNS(DISTINCT(SELECTCOLUMNS(__Table,"__Date",[Date])),"__Count",COUNTROWS(FILTER(__Table,[Date] = [__Date] && ( [__Cost] = BLANK() || [__Cost] = 0 ) )))
RETURN
     MINX(FILTER(__Table1,[__Count] < 1),[__Date])

[Anonymous]

I think that's perfect. Thank you!

I have a slicer with the dates, would it be possible to make the first date displayed in the slicer = to this measure we just calculated (and not show any earlier date than that)?

Thank you

[Greg_Deckler]

@Anonymous To quote old Roblox, "Ooof", defaulting slicers to things is not really a thing.

[brook23]

Thanks for the update.

[v-yiruan-msft]

Hi @Anonymous ,

You can create two measures as below to get it, please find the details in the attachment.

Measure = SUM('Table'[Cost])

Min Date = 
VAR _selpro =
    SELECTEDVALUE ( 'Table'[Product] )
RETURN
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selpro && [Measure] > 0 )
    )

Best Regards