topic Re: Calculating time difference... in Desktop

Calculating time difference...

Anonymous — Wed, 02 Dec 2020 10:28:14 GMT

Hi guys,

I have a dataset with the history of changes to items. Some of those items have been 'tagged' as blocked on certain dates.

Whilst I've managed to calculate the total time an item has spent blocked when the tag is removed, I'm struggling with how do I calculate how long an item that is currently tagged as blocked has been blocked for.

Here is an example from the dataset:

It has been tagged as blocked since 30/11/2020 08:05:31 (so roughly 2 days) - just wondering what my column/measure should be to work this out? Above my Power BI knowledge level 😞

Re: Calculating time difference...

amitchandak — Wed, 02 Dec 2020 11:32:02 GMT

Anonymous , Create a new column like and try

new column =
datediff(maxx(filter(table, [workitemid] = earlier([workitemid]) && [index] = earlier([index]) -1 && [Tagname] =earlier([Tagname]) && [Tagname] ="Blocked"),[ChangedDate]),[ChangedDate] ,minute)/60*24

Re: Calculating time difference...

Anonymous — Wed, 02 Dec 2020 13:37:02 GMT

Thanks, that isn't quite there yet 😞

Entered it in and seeing the following:

I believe the "correct" number should be 2.23, based on NOW()-WorkItemsBlocked[ChangedDate] but using the 08:05 timestamp (as this was the first time in the current collection of 'blocked' tags it was blocked)...

Re: Calculating time difference...

v-kelly-msft — Mon, 07 Dec 2020 06:19:14 GMT

Hi Anonymous ,

First split column "ChangedDate" into 3 columns as below:

Then create a column as below:

Column 2 = var _datetime=IF('Table'[Status]="Blocked",CALCULATE(MAX('Table'[datetime]),FILTER('Table','Table'[WorkItemId]=EARLIER('Table'[WorkItemId])&&'Table'[Status]="Blocked"&&'Table'[Index]<EARLIER('Table'[Index]))),BLANK()) var _time=CALCULATE(MAX('Table'[Time]),FILTER('Table','Table'[datetime]=_datetime)) var _date=CALCULATE(MAX('Table'[ChangedDate.1]),FILTER('Table','Table'[datetime]=_datetime)) var _previousstatus=CALCULATE(MAX('Table'[Status]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1&&'Table'[WorkItemId]=EARLIER('Table'[WorkItemId]))) Return IF('Table'[Status]="Blocked"&&_date<>BLANK()&&_previousstatus="Blocked", IF(DATEDIFF(_date,'Table'[ChangedDate.1],DAY)=0, DATEDIFF(_time,'Table'[Time],HOUR)/24, DATEDIFF(_date,'Table'[ChangedDate.1],DAY)-(24-(HOUR('Table'[Time])+MINUTE('Table'[Time])/60))/24) )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Calculating time difference...

Anonymous — Mon, 07 Dec 2020 17:22:38 GMT

Thanks v-kelly-msft - that has gotten me there 🙂

Only issue is my bar chart still wants to sum the above values:

Any way around this?

Re: Calculating time difference...

v-kelly-msft — Tue, 08 Dec 2020 01:47:46 GMT

Hi Anonymous ,

Create a measure as below:

Measure = SUMX(ALL('Table'),'Table'[Column 2])

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Calculating time difference...

Anonymous — Tue, 08 Dec 2020 10:00:00 GMT

Thanks - unfortunately that doesn't seem to work, and is summing for all the items in that table (there are multiple different work item ID's)...any ideas?

Re: Calculating time difference...

v-kelly-msft — Wed, 09 Dec 2020 00:19:24 GMT

Hi Anonymous ,

Could you pls upload your .pbix file to onedrive business and share the link with us?Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Calculating time difference...

v-kelly-msft — Fri, 11 Dec 2020 07:48:43 GMT

Hi Anonymous ，

If it's not convinient to provide your data,would you pls make a more detailed sample data for me to test?

Or you may try use below measure :

Measure=SUMX(ALLSELECTED('TABLE'[Client Azure DevOps]),'Table'[Column 2])

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Calculating time difference...

Anonymous — Fri, 11 Dec 2020 16:46:30 GMT

Sorry for the delay, here is a link to a file with dummy data. The 'write a blog post' item has been blocked since 27th November but has had updates to it since (but still remains blocked). Thanks 🙂

Re: Calculating time difference...

v-kelly-msft — Tue, 15 Dec 2020 01:20:02 GMT

Hi Anonymous ，

I cant match the data with your expected output,but checking the previous data,with the measure below,it works fine:

Measure = SUMX(VALUES('Table'[WorkItemId]),SUM('Table'[Column 2]))

Would you pls explain more about how to get the result you need based on your .pbix file?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!