Forum Discussion

09's avatar
09
Helper I
4 years ago
Solved

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.

MaterialDateECRT 4400 UNRDiscrepancy Date 
XPSAF513026/01/2022117/01/2022
 10/03/2022117/01/2022
 11/03/2022117/01/2022
 13/03/2022117/01/2022
 14/03/2022117/01/2022
 15/03/2022117/01/2022
 16/03/2022117/01/2022
 17/03/2022117/01/2022
 18/03/2022117/01/2022
 19/03/2022117/01/2022
 20/03/2022117/01/2022
 21/03/2022117/01/2022
 22/03/2022117/01/2022
 23/03/2022117/01/2022
 24/03/2022117/01/2022
 25/03/2022117/01/2022
 27/03/2022117/01/2022
 28/03/2022117/01/2022

NB: I'm workingwith EXCEL.

Thank you for your help!

DAX Calculation
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    09 , Try like

     

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

    • 09's avatar
      09
      Helper I
      4 years ago

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

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      09
      Helper I
      4 years ago

      Thank you. It works perfectly.