Hello,

I’m starting to work with DAX.

I’m calculating the days between IN and OUT for an order.

An order can have several handlings.

If these handlings are in the table. it should consider the days between the IN and OUT date for that record (the lowest level).

If the IN and OUT are not in the matrix, but the order is on it, it should take the days between the min(IN) and max(Out) for that order.

If there is no order in the table, it should should sum the days between the min(IN) and max(Out) for every order.

In table 1, the total should be 78

In table 2 orderID 225731407 handling IN should be 18, INST should be 12. Total should be 62

In table 3, orderID 225731407 should be 18. The total should be 50.

In table 4, the total should be 50

I have this formula

Days Between =

var first_= CALCULATE(min(Sheet1[In]), KEEPFILTERS( Sheet1[OrderID]))

var Last_ = CALCULATE(max(Sheet1[Out]), KEEPFILTERS( Sheet1[OrderID]))

var daysBetween= SUMX(Sheet1, DATEDIFF(first_, Last_, DAY))

Return daysBetween

but it’s not giving the right totals on orderID and in Total

Example ModalSplit.pbix (140.1 KB)

Modalsplit Example.xlsx (9.6 KB)