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 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 😞

Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

  • 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

      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!