Return the discrepancy date per material

[09]

Hello all,

I'm trying to make a DAX measure that returns the first date a discrepancy happens for each material in a pivot table but the formula that i've created didn't give me the right result: CALCULATE(MIN('BD 1'[Date]);FILTER(ALLEXCEPT('BD 1';'BD 1'[Material]);[ECRT 4400 UNR]<>0)). 

[ECRT 4400 UNR]: a measure that allows me to only filter the materials with a stock gap.

This an example of the result that i had.

Material	Date	ECRT 4400 UNR	Discrepancy Date
XPSAF5130	26/01/2022	1	17/01/2022
	10/03/2022	1	17/01/2022
	11/03/2022	1	17/01/2022
	13/03/2022	1	17/01/2022
	14/03/2022	1	17/01/2022
	15/03/2022	1	17/01/2022
	16/03/2022	1	17/01/2022
	17/03/2022	1	17/01/2022
	18/03/2022	1	17/01/2022
	19/03/2022	1	17/01/2022
	20/03/2022	1	17/01/2022
	21/03/2022	1	17/01/2022
	22/03/2022	1	17/01/2022
	23/03/2022	1	17/01/2022
	24/03/2022	1	17/01/2022
	25/03/2022	1	17/01/2022
	27/03/2022	1	17/01/2022
	28/03/2022	1	17/01/2022

NB: I'm workingwith EXCEL.

Thank you for your help!

[tamerj1 · Accepted solution]

Hi @09 

you may try

Measure1 :=
MINX (
    FILTER (
        CALCULATETABLE ( 'BD 1', ALLEXCEPT ( 'BD 1', 'BD 1'[Material] ) ),
        [ECRT 4400 UNR] <> 0
    ),
    'BD 1'[Date]
)

[09]

Thank you. It works perfectly.

[amitchandak]

@09 , Try like

minx(filter(values('BD 1'[Material]),[ECRT 4400 UNR]<>0), calculate( MIN('BD 1'[Date])))

[09]

Thanks for replying. I tried your formula but it gave me that result.