Forum Discussion

harshali's avatar
harshali
Frequent Visitor
9 years ago
Solved

Time Diff between Times in the Same Column

Hi,   I am trying to find the average time between each type of canister change within this table. I am running into some trouble because the dates are in the same column so I cannot just use DATED...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi harshali,

     

    Based on my understanding, you should be able to simply use the formula below to create a measure to calculate the Time Diff, then show the measure with defaultmachine, Canister Type Change, and Type column on the Table visual in your scenario.

     

    Measure = DATEDIFF ( MIN ( Table1[eventdatetime] ), MAX ( Table1[eventdatetime] ), DAY )

     

     

    I know you will feel confused about the solution. So please think about the formula below. :smileyhappy:

     

    (A - B) + (B - C) + (C - D) = A - D

    Hopefully it could help in your scenario.

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi harshali,

 

Based on my understanding, you should be able to simply use the formula below to create a measure to calculate the Time Diff, then show the measure with defaultmachine, Canister Type Change, and Type column on the Table visual in your scenario.

 

Measure = DATEDIFF ( MIN ( Table1[eventdatetime] ), MAX ( Table1[eventdatetime] ), DAY )

 

 

I know you will feel confused about the solution. So please think about the formula below. :smileyhappy:

 

(A - B) + (B - C) + (C - D) = A - D

Hopefully it could help in your scenario.

 

Regards

harshali's avatar
harshali
Frequent Visitor
8 years ago

Hi,

 

Thank you for your help! This exact formula did not work for me as it was giving me zeros for all measure results, but I modified it by dividing the entire formula by a COUNTROWS of the table, and this ended up giving me accurate measurements.