Forum Discussion
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 DATEDIFF between two columns to compute the time. The canister values do not have unique codes either so I am not sure how to go about this. Please let me know if you have any suggestions or solutions.
Thanks.
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
11 Replies
Hi harshali,
What's your expected result against the sample data above? Could you be more precisely with the logic for calculation here?
In addition, it's better to paste the sample data instead of posting a screenshot, so that we can easily use it for testing. :smileyhappy:
Regards
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.
defaultmachine Canister Type Change eventdatetime Index Type Week of Year 16 Yellow Canister Change 2017-05-16T10:21:08.874143 1 1 21 16 Yellow Canister Change 2017-06-11T10:19:26.219861 2 1 24 16 White Canister Change 2017-06-11T10:17:47.969449 3 3 24 16 White Canister Change 2017-08-25T13:06:55.639283 4 3 35 16 White Canister Change 2017-05-16T10:18:26.854676 5 3 21 16 Thinner Canister Change 2017-05-16T10:23:00.467694 6 6 21 16 Thinner Canister Change 2017-06-11T10:20:59.799339 7 6 24 16 Thinner Canister Change 2017-08-25T13:00:45.456448 8 6 35 16 Red Canister Change 2017-06-11T10:19:47.215559 9 2 24 16 Red Canister Change 2017-05-16T10:21:33.900915 10 2 21 16 Red Canister Change 2017-08-25T13:12:25.556772 11 2 35 16 Moisturizer Canister Change 2017-08-25T13:18:36.474456 12 5 35 16 Moisturizer Canister Change 2017-05-16T10:21:58.935576 13 5 21 When it comes to Dates & Times, 'Average' is a term with multiple meanings. I read your reuqest asking for 'How many Days on Average does each canister last?) Here's a Calcualted Column (not a measure) to help you find the DATEDIFF between the MIN and MAX values. I then 'Average' by summing the DATEDIFF days and dividing by the UniqueCount of all Types...
Thoughts? P.S. Your raw data is in the format your provided... My data screen shot on top just breaks things up into EARLIEST and LATEST so I could do a gut check on my DATEDIFF values.
DIFF by Type = CALCULATE( DATEDIFF(MIN(Table1[eventdatetime]),MAX(Table1[eventdatetime]),DAY), FILTER(Table1, Table1[Type] = EARLIER(Table1[Type])))
