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

Hi v-ljerr-msft,

 

Sorry about that. My ideal result would be the time difference between rows, based on the same Type and Index number. I have also pasted the data below for your convenience. Thanks for your advice.

 

defaultmachineCanister Type ChangeeventdatetimeIndexTypeWeek of Year
16Yellow Canister Change2017-05-16T10:21:08.8741431121
16Yellow Canister Change2017-06-11T10:19:26.2198612124
16White Canister Change2017-06-11T10:17:47.9694493324
16White Canister Change2017-08-25T13:06:55.6392834335
16White Canister Change2017-05-16T10:18:26.8546765321
16Thinner Canister Change2017-05-16T10:23:00.4676946621
16Thinner Canister Change2017-06-11T10:20:59.7993397624
16Thinner Canister Change2017-08-25T13:00:45.4564488635
16Red Canister Change2017-06-11T10:19:47.2155599224
16Red Canister Change2017-05-16T10:21:33.90091510221
16Red Canister Change2017-08-25T13:12:25.55677211235
16Moisturizer Canister Change2017-08-25T13:18:36.47445612535
16Moisturizer Canister Change2017-05-16T10:21:58.93557613521
fhill's avatar
fhill
Resident Rockstar
8 years ago

Can we get more data?  Will multiple DefaultMachine numbers be possible, and will Index number continue over DefaultMachines or reset for each DefaultMachine?

  • 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
    • fhill's avatar
      fhill
      Resident Rockstar
      8 years ago

      I've floated this issue in hopes someone else will try, I can't seem to get the code working with the multiple variables... FOrrest

    • 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.