Re: Complex Calculated Column summing time

Anonymous · ‎07-09-2020

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 each WorkItemId, scan for text "Blocked" in field TagNames and do some date calculation.

I have specified three scenarios for the WorkItemId 1, 2 and 3.

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
         1        7     0 06/19/2020 11.41.04  True               See calculation 1  
         1        6     1 06/19/2020 11.41.04  False     Blocked
         1        5     2 06/18/2020 10.41.23  False     Blocked
         1        4     3 06/17/2020 09.38.54  False     
         1        3     4 06/16/2020 14.22.21  False     Blocked
         1        2     5 06/15/2020 15.01.02  False     
         1        1     6 06/14/2020 07.21.16  False     
         2        6     7 07/07/2020 09:58:12  True      Blocked  See calculation 2  
         2        5     8 07/07/2020 09:58:12  False     
         2        4     9 07/06/2020 10:22:02  False     Blocked
         2        3    10 07/05/2020 12:34:31  False     
         2        2    11 07/04/2020 13:51:30  False     Blocked
         2        1    12 07/03/2020 08:23:41  False     
         3        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, calculation for summing up total blocked time for the "BlockedTime" column in row where IsCurrent = True.

I've specified calculation examples below.

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?

Anonymous · ‎07-09-2020

here a draft on which you can make the necessary changes and adjustments ...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZI9DsIwDIXv0hkJ/8Rt6pGBE7AhBgRMICGBuD9OSIMpVQYrqeKvz+8l+32H3aobrMAKxzX0awLKH6gBFYJtd4/XxZbusPr09+m40b893p4J2Nzup+vlXDmxotQaPQeJI25w6Z/pHAfHwagcVbxeBbhA2HuhoERK2BCiMiSK50TBjNGSUE0j+MkGU1Hs5wCV5hy3WRmcFYmK9I3aDzYNFRvYn05yPxbAR20J/DiZC+WYoYBSQVIOyriklC8UCxEqwSqoDA2pnB8VkKupaG/BXsRciieCC0HOld2PyEJ6hzc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, Revision = _t, Index = _t, AnalyticsUpdatedDate = _t, IsCurrent = _t, TagNames = _t, BlockedTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"Revision", Int64.Type}, {"Index", Int64.Type}, {"AnalyticsUpdatedDate", type date}, {"IsCurrent", type text}, {"TagNames", type logical}, {"BlockedTime", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "calculations", each if [TagNames] then 
    
    let 
    tab=Table.SelectRows(#"Changed Type",(r)=> _[WorkItemId]=r[WorkItemId]),
   currentDate= Date.From(DateTime.LocalNow()),
     nrows=Table.RowCount(tab), 
     aud=tab[AnalyticsUpdatedDate],
     bt=tab[BlockedTime],
     tn=tab[TagNames],
     zt=#duration(0,0,0,0)
    in List.Accumulate({0..nrows-1},zt,(s,c)=>s+(if (bt{c}<>"" and tn{c}) then currentDate-aud{c} else if bt{c}<>"" then aud{c}-aud{c+1} else zt)) 
    
    
    else "")
in
    #"Added Custom"

#################### edited and adjusted #####################