Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
harshali
Frequent Visitor

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.

 

Canister.PNG

 

Thanks.

1 ACCEPTED SOLUTION

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 defaultmachineCanister Type Change, and Type column on the Table visual in your scenario.

 

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

r2.PNG

 

 

I know you will feel confused about the solution. So please think about the formula below. Smiley Happy

 

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

Hopefully it could help in your scenario.

 

Regards

View solution in original post

11 REPLIES 11
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

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.

 

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
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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

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 defaultmachineCanister Type Change, and Type column on the Table visual in your scenario.

 

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

r2.PNG

 

 

I know you will feel confused about the solution. So please think about the formula below. Smiley Happy

 

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

Hopefully it could help in your scenario.

 

Regards

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.

fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




fhill
Resident Rockstar
Resident Rockstar

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])))

 

Capture.PNG




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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. 

I am going to test this now. But how did you remove the T in the eventdatetime column?

fhill
Resident Rockstar
Resident Rockstar

Power BI automaticlaly converted that to a Date Time Column for me...?  Not sure if this is a USA formatting thing, as I've seen the "T" allot but never experienced it in my data personally.




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.