Skip to main content
cancel
Showing results for 
Search instead 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

Reply
09
Helper I
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.

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!

1 ACCEPTED SOLUTION
tamerj1
Super User
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]
)

 

View solution in original post

4 REPLIES 4
tamerj1
Super User
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]
)

 

Thank you. It works perfectly.

amitchandak
Super User
Super User

@09 , Try like

 

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

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

09_0-1648471385255.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.