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)
