Forum Discussion

Taro_Gulat's avatar
Taro_Gulat
Regular Visitor
1 year ago
Solved

Date Difference

Hi all, 

 

I need to calculate the sum of timedifference in below scenario:

I need to calculate the sum of difference between start date & end date of step = A (take minimum start date for each type) and Step = B (take minimum end date for each type). for example: difference between start date & end date of row 1 and 2, and difference between start date & end date of row 4 and 6. Record inside step are static (always A, B) but type can be more. In this case it will be 01:10:00

 

I am having difficulties due to blank values in start date and end date. Not able to pick the correct record. 

 

can anyone give some suggestion?

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Taro_Gulat 

     

    Thank you very much ryan_mayu for your prompt reply.

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create a measure.

     

    Date Difference = 
VAR MinStartDateA = 
CALCULATE(
    MIN('Table'[Start Date]),
    FILTER(ALL('Table'), 'Table'[Type] = MAX('Table'[Type]) && 'Table'[Step] = "A")
)
VAR MinEndDateB = 
CALCULATE(
    MIN('Table'[End Date]),
    FILTER(ALL('Table'), 'Table'[Type] = MAX('Table'[Type]) && 'Table'[Step] = "B")
)
VAR TimeDifference = DATEDIFF(MinStartDateA, MinEndDateB, SECOND)
RETURN
IF(
    SELECTEDVALUE('Table'[End Date]) = MinEndDateB,
    FORMAT(INT(TimeDifference / 3600), "00") 
    & ":" & 
    FORMAT(INT(MOD(TimeDifference, 3600) / 60), "00") 
    & ":" & 
    FORMAT(MOD(TimeDifference, 60), "00"),
    BLANK()
)

     

    Here is the result.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Taro_Gulat 

     

    Thank you very much ryan_mayu for your prompt reply.

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    Create a measure.

     

    Date Difference = 
VAR MinStartDateA = 
CALCULATE(
    MIN('Table'[Start Date]),
    FILTER(ALL('Table'), 'Table'[Type] = MAX('Table'[Type]) && 'Table'[Step] = "A")
)
VAR MinEndDateB = 
CALCULATE(
    MIN('Table'[End Date]),
    FILTER(ALL('Table'), 'Table'[Type] = MAX('Table'[Type]) && 'Table'[Step] = "B")
)
VAR TimeDifference = DATEDIFF(MinStartDateA, MinEndDateB, SECOND)
RETURN
IF(
    SELECTEDVALUE('Table'[End Date]) = MinEndDateB,
    FORMAT(INT(TimeDifference / 3600), "00") 
    & ":" & 
    FORMAT(INT(MOD(TimeDifference, 3600) / 60), "00") 
    & ":" & 
    FORMAT(MOD(TimeDifference, 60), "00"),
    BLANK()
)

     

    Here is the result.

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.