Forum Discussion
Time Diff between Times in the Same Column
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
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])))
Isn't this only providing the duration between the oldest and most recent change though? For example, for the White Canister change, I noticed it took the difference between 5/16 and 8/25 rather than 6/11 and 8/25 which would be the most recent change and the second most recent change. Ideally I would like for it to take the difference like this-- between the most recent and second most recent, not most recent and oldest.