Forum Discussion

harshali's avatar
harshali
Frequent Visitor
8 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

harshali's avatar
harshali
Frequent Visitor
8 years ago

Looks like the index numbers are not coming out consecutively as I had expected, but yes they continue over machine numbers. And there are multiple machine numbers as well. Also the unit of time of time that is produced is the number of days, correct? 

 

Here is some more data that will hopefully help. Thanks again.

 

defaultmachineCanister Type ChangeeventdatetimeTypeIndex
19Red Canister Change2017-08-24T11:41:50.5364422897101
19Red Canister Change2017-08-24T11:14:34.7678562897104
19Red Canister Change2017-08-24T11:08:06.704352897108
19Red Canister Change2017-08-24T11:02:36.4107952897109
22Moisturizer Canister Change2017-08-21T16:36:25.75401851008016
22Black Canister Change2017-08-21T16:32:21.26553441008024
22White Canister Change2017-08-21T16:30:10.19948931007984
22Yellow Canister Change2017-08-21T10:12:24.65115511007967
22Thinner Canister Change2017-08-19T19:41:11.76630461007992
22White Canister Change2017-08-19T16:26:02.05908631007989
22Yellow Canister Change2017-08-18T17:42:35.50639511007956
19Moisturizer Canister Change2017-08-15T14:41:35.4085755897113
19Thinner Canister Change2017-08-15T14:37:14.4809176897097
19Thinner Canister Change2017-08-15T14:34:14.4411716897096
19Thinner Canister Change2017-08-15T14:31:39.5082816897100
19Red Canister Change2017-08-15T14:23:36.1234322897105
22White Canister Change2017-08-15T12:29:17.29265931007983
19Red Canister Change2017-08-14T16:09:52.6403782897107
19White Canister Change2017-08-14T14:10:36.3582763897087
19Moisturizer Canister Change2017-08-14T14:08:24.0856315897114
19Black Canister Change2017-08-14T14:06:09.4448544897118
19Red Canister Change2017-08-14T14:04:48.9890882897112
22Thinner Canister Change2017-08-13T15:51:37.80195961007997
22Yellow Canister Change2017-08-13T14:21:57.86552411007968
22Red Canister Change2017-08-12T10:03:41.94607121008010
22White Canister Change2017-08-09T14:52:06.35258331007986
22Thinner Canister Change2017-08-07T15:57:16.96003461007996
22Yellow Canister Change2017-08-06T17:42:01.5367311007971
22Black Canister Change2017-08-05T16:16:01.89979441008023
22White Canister Change2017-08-02T09:23:04.43083131007981
22Yellow Canister Change2017-08-01T14:39:09.39181611007960
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.