cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Return the discrepancy date per material

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.

1 ACCEPTED SOLUTION
Super User

Hi @09

you may try

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

4 REPLIES 4
Super User

Hi @09

you may try

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

Helper I

Thank you. It works perfectly.

Super User

@09 , Try like

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

Helper I

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