Forum Discussion

AlirezaEsmati's avatar
AlirezaEsmati
Regular Visitor
4 years ago
Solved

Help needed for Date difference Dax

Hello everyone, I need help calculating the lead time between two steps of each specific order in front of that step using a calculated column or a measure. Thanks in advance
Need Help
  • danextian's avatar
    danextian
    4 years ago

    My bad, that should have been:

     

    calc column =
CALCULATE (
    MAX ( table[Date] ),
    FILTER ( ALL ( table[Step] ), table[Step] = "C" ),
    ALLEXCEPT ( table, table[order] )
)
    - CALCULATE (
        MAX ( table[Date] ),
        FILTER ( ALL ( table[Step] ), table[Step] = "A" ),
        ALLEXCEPT ( table, table[order] )
    )

    or

    calc column =
CALCULATE (
    MAX ( table[Date] ),
    table[Step] = "C",
    ALLEXCEPT ( table, table[order] )
)
    - CALCULATE (
        MAX ( table[Date] ),
        table[Step] = "A",
        ALLEXCEPT ( table, table[order] )
    )
danextian's avatar
danextian
Icon for Super User rankSuper User
4 years ago

Hi AlirezaEsmati 

 

Try something like:

 

calc column =
CALCULATE (
    MAX ( table[Date] ),
    ALL ( table[Step] ) = "C",
    ALLEXCEPT ( table, table[order] )
)
    - CALCULATE (
        MAX ( table[Date] ),
        ALL ( table[Step] ) = "A",
        ALLEXCEPT ( table, table[order] )
    )

 

The same logic can be used for the other step.

AlirezaEsmati's avatar
AlirezaEsmati
Regular Visitor
4 years ago

Thanx danextian 
It errors for 

 

ALL ( table[Step] ) = "C"

 

 >> The True/False expression does not specify a column. Each True/False expression used as a table filter expression must refer to exactly one column.

- I changed it to this:

calc_column = 
CALCULATE (
    MAX (ordertable[Date]),
    FILTER(Ordertable,Ordertable[Step] = "C"),
   GROUPBY(Ordertable,Ordertable[Order No])
)
-
    CALCULATE (
        MAX (ordertable[Date]),
        FILTER(Ordertable,Ordertable[Step] = "A"),
        GROUPBY(Ordertable,Ordertable[Order No])
    )

but it results in this:

 

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    4 years ago

    My bad, that should have been:

     

    calc column =
CALCULATE (
    MAX ( table[Date] ),
    FILTER ( ALL ( table[Step] ), table[Step] = "C" ),
    ALLEXCEPT ( table, table[order] )
)
    - CALCULATE (
        MAX ( table[Date] ),
        FILTER ( ALL ( table[Step] ), table[Step] = "A" ),
        ALLEXCEPT ( table, table[order] )
    )

    or

    calc column =
CALCULATE (
    MAX ( table[Date] ),
    table[Step] = "C",
    ALLEXCEPT ( table, table[order] )
)
    - CALCULATE (
        MAX ( table[Date] ),
        table[Step] = "A",
        ALLEXCEPT ( table, table[order] )
    )