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] )
    )

3 Replies

  • danextian's avatar
    danextian
    Super 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
        Super 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] )
    )