Calculating time difference...

[Anonymous]

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 😞

[Anonymous]

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 🙂

[v-kelly-msft]

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!

[Anonymous]

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

[v-kelly-msft]

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!

[Anonymous]

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?

[v-kelly-msft]

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!

[Anonymous]

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?

[v-kelly-msft]

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!

[v-kelly-msft]

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!

[amitchandak]

@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