topic Return the discrepancy date per material in DAX Commands and Tips

Return the discrepancy date per material

09 — Mon, 28 Mar 2022 11:35:39 GMT

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!

Re: Return the discrepancy date per material

amitchandak — Mon, 28 Mar 2022 12:36:41 GMT

09 , Try like

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

Re: Return the discrepancy date per material

09 — Mon, 28 Mar 2022 12:43:28 GMT

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

Re: Return the discrepancy date per material

tamerj1 — Mon, 28 Mar 2022 14:42:14 GMT

Hi 09

you may try

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

Re: Return the discrepancy date per material

09 — Mon, 28 Mar 2022 15:24:57 GMT

Thank you. It works perfectly.