Power BI Complex Calculated Column

Question

I'm quite new to Power BI and I try to query Azure DevOps data for a Board with historical data.

I have made an OData feed that queries data and returns the data below (see table) and for each row where IsCurrent = True, I want to calculate the "blocked time" into a new Column BlockedTime for that WorkItemId. So I need to traverse records for that WorkItemId and do date calculation.

I am kind of stuck currently after much delving into CALCULATE, Filters and more.

I have specified three scenarios for the WorkItemId 1, 72 and 149.

I use the column Index as the number to reference lines in my calculations below.

My query returns the following:

WorkItemId Revision Index AnalyticsUpdatedDate IsCurrent TagNames BlockedTime
        72        7     0 06/19/2020 11.41.04  True               See calculation 1  
        72        6     1 06/19/2020 11.41.04  False     Blocked
        72        5     2 06/18/2020 10.41.23  False     Blocked
        72        4     3 06/17/2020 09.38.54  False     
        72        3     4 06/16/2020 14.22.21  False     Blocked
        72        2     5 06/15/2020 15.01.02  False     
        72        1     6 06/14/2020 07.21.16  False     
         1        6     7 07/07/2020 09:58:12  True      Blocked  See calculation 2  
         1        5     8 07/07/2020 09:58:12  False     
         1        4     9 07/06/2020 10:22:02  False     Blocked
         1        3    10 07/05/2020 12:34:31  False     
         1        2    11 07/04/2020 13:51:30  False     Blocked
         1        1    12 07/03/2020 08:23:41  False     
        149       1    13 07/02/2020 10:01:55  False     Blocked See calculation 3

RULES

Variable CurrentDate contains current DateTime

When TagNames contains "Blocked" text for a given line it is considered blocked from the date/time in AnalyticsUpdatedDate and the time should be calculated "backwards" by traversing down in the records (Revision number going down) and summarized until a line without "Blocked" in TagNames is encountered

Calculation 1: Calculate BLOCKED TIME and put the result into BlockedTime(0)

(AnalyticsUpdatedDate(1) - AnalyticsUpdatedDate(2)) + 
(AnalyticsUpdatedDate(2) - AnalyticsUpdatedDate(3)) + 
(AnalyticsUpdatedDate(4) - AnalyticsUpdatedDate(5))

Calculation 2: Calculate BLOCKED TIME and put the result into BlockedTime(7)

(CurrentDate - AnalyticsUpdatedDate(7)) + 
(AnalyticsUpdatedDate(9) - AnalyticsUpdatedDate(10)) + 
(AnalyticsUpdatedDate(11) - AnalyticsUpdatedDate(12))

Calculation 3: Calculate BLOCKED TIME and put the result into BlockedTime(13)

(CurrentDate - AnalyticsUpdatedDate(13))

Anyone who has any ideas of how this could be solved best?

Are those 3 all posible results? If not, Is there a logic to aply an especific calcuation to a work item? Why the last record have a calculate result? I that blocked Time Is calculate only when iscurrent Is true. — Agustin Palacios

Aldert Aldert · Accepted Answer · 2020-07-09T20:41:16

So here we go. I made it in 3 steps with 3 columns so it is a bit easier to follow, you can make it in one script.

First we need to find the dates of all teh releases (when the block is not there anymore)

This we do with the following column:

Released = 
var workItemId = Track[WorkItemId]
var Revision = Track[Revision]
var ReleaseTime = CALCULATE(MIN(Track[AnalyticsUpdatedDate]), FILTER(Track, Track[WorkItemId] = workItemId && Revision < Track[Revision]))
var ReleaseFinal = if (ISBLANK(ReleaseTime), NOW(), ReleaseTime)
return if(Track[TagNames] = "Blocked", ReleaseFinal)

In the calculate, I am getting the first row (MIN) where the item ensuring I have the same workItemId with a release higher.

If I do not find a releaseTime, it means the item is still blocked so I populate the ReleaseFinal with NOW().

As I only want output when the item is blocked, I have an if on the last return

Next I create a new column to calculate the difference in seconds:

SecDiff = DATEDIFF(Track[AnalyticsUpdatedDate],Track[Released], SECOND)

The last column I created to sum the seconds together and poplulate the column with the IsCurrent = True:

TotalBlockedTime = 
var WorkItemId = Track[WorkItemId]
return if (Track[IsCurrent], CALCULATE(SUM(Track[SecDiff]), FILTER(Track, Track[WorkItemId] = WorkItemId)))

End result, see below. I think your data is not 100% because your 3rd example Iscurrent = false (what should be true).

Enjoy!

Power BI Complex Calculated Column

1 Answers