Calculation between rows

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.

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: