How to calculate date difference between earliest date field and normal date field

[Thankyouverymuc]

Dear Power BI community, 

I am trying to solve how you can calculate the # of days between 2 dates when 1 column has duplicates and I want to select the earliest date from column 1 (Moment warehouse) + the date from column 'Delivery status' to calculate the # of days betweent hem when the Status = '2'. 

This is how my data looks like (excel example from PBI) + what I would like (see last column in the screenshot):

I tried several options with the datediff functions but somehow I can't fix it and I would be really happy if someone can help me out with this one. I tried this formula:

Test=

VAR x =

    MIN(

    FILTER(ALL('Table1),'Table1'[Delivery-status].[Date] = min('Table1'[Delivery-status].[Date]) && 'Table1'[Status] = "2"),

'Table1'[Delivery-status].[Date]

)

VAR y = 'Table1'[Moment warehouse].[Date]

RETURN

DIVIDE(datediff(x,y,DAY),1)

Thank you guys in advance! Much appreciated.

[tamerj1]

 Hi @Thankyouverymuc 

You mentioned "when the Status = '2'. " But you are showing a value at status 4?

[Thankyouverymuc]

You're right - I corrected it - only values when the status = '2'. 

@tamerj1 , do you have a clue how I can fix this DAX formula? 

[tamerj1]

@Thankyouverymuc 

Please try

Test =
VAR CurrentNumberTable =
    CALCULATETABLE ( 'Table1', ALLEXCEPT ( 'Table1', 'Table1'[Number] ) )
VAR FilteredTable =
    FILTER ( CurrentNumberTable, 'Table1'[Status] = 2 )
VAR MinWarehouseDate =
    MINX ( FilteredTable, 'Table1'[Moment warehouse] )
VAR MinDeliveryDate =
    MINX ( FilteredTable, 'Table1'[Delivery-status] )
RETURN
    DATEDIFF ( MinDeliveryDate, MinWarehouseDate, DAY )

[Thankyouverymuc]

@tamerj1 , the formula works except if the status <> "2"(string field). Do you know how that's solved? Because when the status is not "2" , I would like to see nothing (blank/ "")

[tamerj1 · Accepted solution]

@Thankyouverymuc 

Here you go

Test =
VAR CurrentNumberTable =
    CALCULATETABLE (
        'Table1',
        ALLEXCEPT ( 'Table1', 'Table1'[Number], 'Table1'[Status] )
    )
VAR MinWarehouseDate =
    MINX ( CurrentNumberTable, 'Table1'[Moment warehouse] )
VAR MinDeliveryDate =
    MINX ( CurrentNumberTable, 'Table1'[Delivery-status] )
RETURN
    DATEDIFF ( MinDeliveryDate, MinWarehouseDate, DAY )

[Thankyouverymuc]

Thank you so much @tamerj1 , I tried row per row and I completely understand the formula now. Have a great day!