Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Calculating time difference...

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 sp...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-kelly-msft
Community Support
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-kelly-msft
      Community Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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?