Calculation between rows

[Anonymous]

Hi Guys,

Need your help with this dataset I'm working with. I have queried items that contained a tag of 'blocked' and I'm trying to calculate the amount of time lost to being blocked. Dataset like so:

So in this case it would be roughly 3+ days lost when this item had a tag of blocked.

Any tips on how this could be calculated?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you need to create an index column for each [WorkItemId] in power query, here is the whole query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZMxDoMwDEXvkhnJ2P4JsPYaiAk6gcT9NzK0Vas2+QxVp0rZ3osdWz/jGKzHEFNoQj7Wai8qhjA1p8hl2+f1utwFk47wgXBlDTT9he8I1jLBqoIL45Hw9DlmTlKUGzPB67OB5BCi9dnAfsK7cIrEH5HC5h9EzVGIFeGR8I7V91fhidRLgzwNt7iWeaEzBOQie1je6XQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, TagNames = _t, ChangedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"TagNames", type text}, {"ChangedDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WorkItemId"}, {{"Count", each _, type table [WorkItemId=nullable number, TagNames=nullable text, ChangedDate=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TagNames", "ChangedDate", "Index"}, {"Custom.TagNames", "Custom.ChangedDate", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.TagNames", "TagNames"}, {"Custom.ChangedDate", "ChangedDate"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"TagNames", type text}, {"ChangedDate", type date}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Close and apply it in power query. Create these two auxiliary calculated columns using dax:

Tag = 
VAR next =
    CALCULATE (
        MAX ( 'Table'[TagNames] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR tag =
    IF (
        'Table'[TagNames] = BLANK (),
        2,
        IF ( 'Table'[TagNames] = "Blocked" && 'Table'[TagNames] = next, 1, 0 )
    )
RETURN
    tag

Partition = 
IF (
    'Table'[TagNames] = "Blocked",
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Tag] = 0
        ),
        'Table'[ChangedDate],
        ,
        ASC,
        DENSE
    )
)

Create this measure to get the expected result:

Datediff = 
VAR tab =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[TagNames] = "Blocked" ),
        'Table'[WorkItemId],
        'Table'[ChangedDate],
        'Table'[Partition],
        "Datediff",
            CALCULATE (
                DATEDIFF (
                    CALCULATE (
                        MIN ( 'Table'[ChangedDate] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[WorkItemId]
                                IN DISTINCT ( 'Table'[WorkItemId] )
                                    && 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Table'[ChangedDate] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[WorkItemId]
                                IN DISTINCT ( 'Table'[WorkItemId] )
                                    && 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
                        )
                    ),
                    DAY
                ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                        && 'Table'[Partition] = EARLIER ( 'Table'[Partition] )
                )
            )
    )
VAR tb =
    SUMMARIZE ( tab, [WorkItemId], [Datediff] )
RETURN
    SUMX (
        FILTER ( tb, 'Table'[WorkItemId] IN DISTINCT ( 'Table'[WorkItemId] ) ),
        [Datediff]
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

I created a measure, you keep the Title, and this measure on a Table visual. It will show you the days lost:

Day Lost = 
var __t = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            FILTER(Table13,Table13[TagNames] = "Blocked"),
            "Date1",  DATE(YEAR(Table13[ChangedDate]),MONTH(Table13[ChangedDate]),DAY(Table13[ChangedDate]))
        ),
    "Date2",[Date1]
    )
return
COUNTROWS(DISTINCT(__t))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks - that's getting there. Although not quite sure that's correct.

For example if I take ID 284956

Yet in the dataset, this was Blocked from 7th Feb - 6th March (27 days) and 27 March - 24th April (28 days).

So I'm expecting to see 55 days not 14?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you need to create an index column for each [WorkItemId] in power query, here is the whole query code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZMxDoMwDEXvkhnJ2P4JsPYaiAk6gcT9NzK0Vas2+QxVp0rZ3osdWz/jGKzHEFNoQj7Wai8qhjA1p8hl2+f1utwFk47wgXBlDTT9he8I1jLBqoIL45Hw9DlmTlKUGzPB67OB5BCi9dnAfsK7cIrEH5HC5h9EzVGIFeGR8I7V91fhidRLgzwNt7iWeaEzBOQie1je6XQA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, TagNames = _t, ChangedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"TagNames", type text}, {"ChangedDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"WorkItemId"}, {{"Count", each _, type table [WorkItemId=nullable number, TagNames=nullable text, ChangedDate=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TagNames", "ChangedDate", "Index"}, {"Custom.TagNames", "Custom.ChangedDate", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.TagNames", "TagNames"}, {"Custom.ChangedDate", "ChangedDate"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"TagNames", type text}, {"ChangedDate", type date}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Close and apply it in power query. Create these two auxiliary calculated columns using dax:

Tag = 
VAR next =
    CALCULATE (
        MAX ( 'Table'[TagNames] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
VAR tag =
    IF (
        'Table'[TagNames] = BLANK (),
        2,
        IF ( 'Table'[TagNames] = "Blocked" && 'Table'[TagNames] = next, 1, 0 )
    )
RETURN
    tag

Partition = 
IF (
    'Table'[TagNames] = "Blocked",
    RANKX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Tag] = 0
        ),
        'Table'[ChangedDate],
        ,
        ASC,
        DENSE
    )
)

Create this measure to get the expected result:

Datediff = 
VAR tab =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[TagNames] = "Blocked" ),
        'Table'[WorkItemId],
        'Table'[ChangedDate],
        'Table'[Partition],
        "Datediff",
            CALCULATE (
                DATEDIFF (
                    CALCULATE (
                        MIN ( 'Table'[ChangedDate] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[WorkItemId]
                                IN DISTINCT ( 'Table'[WorkItemId] )
                                    && 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
                        )
                    ),
                    CALCULATE (
                        MAX ( 'Table'[ChangedDate] ),
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[WorkItemId]
                                IN DISTINCT ( 'Table'[WorkItemId] )
                                    && 'Table'[Partition] IN DISTINCT ( 'Table'[Partition] )
                        )
                    ),
                    DAY
                ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                        && 'Table'[Partition] = EARLIER ( 'Table'[Partition] )
                )
            )
    )
VAR tb =
    SUMMARIZE ( tab, [WorkItemId], [Datediff] )
RETURN
    SUMX (
        FILTER ( tb, 'Table'[WorkItemId] IN DISTINCT ( 'Table'[WorkItemId] ) ),
        [Datediff]
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks - I need some time to work through and understand your guidance...but looks promising 🙂

[Anonymous · Accepted solution]

The approach I took was as follows:

1) Added an index column

2) Added a column:

BlockedItems = IF(SEARCH("Block", WorkItemsBlocked[TagNames],1,0), 1,0)

3) Added another column:

NumberofBlockedDays = 
VAR myindex = WorkItemsBlocked[Index]
VAR myworkitemid = WorkItemsBlocked[WorkItemId]
VAR previousindex =
    CALCULATE (
        MAX ( WorkItemsBlocked[Index] ),
        FILTER ( WorkItemsBlocked, WorkItemsBlocked[WorkItemId] = myworkitemid && WorkItemsBlocked[Index] < myindex )
    )
VAR previousdate =
    CALCULATE (
        MAX ( WorkItemsBlocked[ChangedDate] ),
        FILTER (
            WorkItemsBlocked,
            WorkItemsBlocked[Index] = previousindex
                && WorkItemsBlocked[WorkItemId] = myworkitemid
                    && WorkItemsBlocked[BlockedItems] = VALUE(1)
        )
    )
RETURN
    IF ( previousdate, WorkItemsBlocked[ChangedDate] - previousdate )

With my results: