how to plus the latest row data of column with previous column

[SherryZhao]

Hi all, hope you all well. It's hard to descripe my problem, so I show in the table. I have the left column and want to get the right column. The logic in excel is shown in the pictures. Thanks for your great support in advance.

Count	Result
0	0
1	1
1	2
1	3
0	0
1	1
1	2
0	0
1	1
1	2
0	0
1	1
1	2
1	3
1	4
1	5

[lbendlin · Accepted solution]

Power BI has no concept of "latest row" or "previous column".  You need to provide guidance yourself, for example via index columns.

Here is a Power Query implementation that uses GroupKind.Local (credit to @ImkeF )

let
    SM = (tb) => let 
       #"Added Index" = Table.AddIndexColumn(tb, "Index", 0, 1, Int64.Type),
       #"Added Custom" = Table.AddColumn(#"Added Index", "Result", each List.Sum(List.FirstN(#"Added Index"[Count],[Index]+1)),Int64.Type)
    in #"Added Custom",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMsQgMcWJEUEjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Count"}, {{"Count.1", each _, type table [Count=nullable number]}},GroupKind.Local,(x,y)=>Number.From(x=y)),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each SM([Count.1])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Count", "Result"}, {"Count", "Result"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[lbendlin · Accepted solution]

Power BI has no concept of "latest row" or "previous column".  You need to provide guidance yourself, for example via index columns.

Here is a Power Query implementation that uses GroupKind.Local (credit to @ImkeF )

let
    SM = (tb) => let 
       #"Added Index" = Table.AddIndexColumn(tb, "Index", 0, 1, Int64.Type),
       #"Added Custom" = Table.AddColumn(#"Added Index", "Result", each List.Sum(List.FirstN(#"Added Index"[Count],[Index]+1)),Int64.Type)
    in #"Added Custom",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMsQgMcWJEUEjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Count"}, {{"Count.1", each _, type table [Count=nullable number]}},GroupKind.Local,(x,y)=>Number.From(x=y)),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each SM([Count.1])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Count", "Result"}, {"Count", "Result"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[SherryZhao]

Dear Ibendlin, first of all, really appreciate your quick response. I run your code in Power Query and it works. But actually, I got the left column in DAX but not in Power Query, so I wonder if there a DAX solution to get the right column? Thanks.

[v-yiruan-msft]

Hi @SherryZhao ,

Besides that the calculated column [Count], is there any other field in your table? Could you please provide some raw data(exclude sensitive data) in your table, later we will check if we can achieve it by DAX. Thank you.

Best Regards

[SherryZhao]

Dear Rena,

It's ok to share with you the raw data. I actually want to get count between each "e_Clean".

Event	CompletedDate	Count	Result
e_Clean	1/5/2019 8:53	0	0
e_In Use	1/7/2019 17:08	1	1
e_In Use	1/8/2019 13:35	1	2
e_In Use	1/11/2019 7:08	1	3
e_Clean	1/11/2019 15:58	0	0
e_In Use	1/12/2019 10:55	1	1
e_In Use	1/12/2019 23:54	1	2
e_Clean	1/13/2019 6:58	0	0

Feel free to reach me out if more info needed. Thanks for your effort.

Best regards,

Sherry

[v-yiruan-msft · Accepted solution]

Hi @SherryZhao ,

You can create a calculated column as below to get it, please find the details in the attachment.

Result = 
VAR _preclean =
    CALCULATE (
        MAX ( 'Table'[CompletedDate] ),
        FILTER (
            'Table',
            'Table'[Event] = "e_Clean"
                && 'Table'[CompletedDate] < EARLIER ( 'Table'[CompletedDate] )
        )
    )
VAR _nextclean =
    CALCULATE (
        MIN ( 'Table'[CompletedDate] ),
        FILTER (
            'Table',
            'Table'[Event] = "e_Clean"
                && 'Table'[CompletedDate] > EARLIER ( 'Table'[CompletedDate] )
        )
    )
RETURN
    IF (
        'Table'[Event] = "e_Clean",
        0,
        CALCULATE (
            SUM ( 'Table'[Count] ),
            FILTER (
                'Table',
                'Table'[Event] = EARLIER ( 'Table'[Event] )
                    && 'Table'[CompletedDate] <= EARLIER ( 'Table'[CompletedDate] )
                    && 'Table'[CompletedDate] > _preclean
                    && 'Table'[CompletedDate] < _nextclean
            )
        )
    )

Or you can also create a measure as below with similar logic to get it:

Measure = 
VAR _selevent =
    SELECTEDVALUE ( 'Table'[Event] )
VAR _selcmdate =
    SELECTEDVALUE ( 'Table'[CompletedDate] )
VAR _preclean =
    CALCULATE (
        MAX ( 'Table'[CompletedDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Event] = "e_Clean"
                && 'Table'[CompletedDate] < _selcmdate
        )
    )
VAR _nextclean =
    CALCULATE (
        MIN ( 'Table'[CompletedDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Event] = "e_Clean"
                && 'Table'[CompletedDate] > _selcmdate
        )
    )
RETURN
    IF (
        _selevent = "e_Clean",
        0,
        CALCULATE (
            SUM ( 'Table'[Count] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Event] = _selevent
                    && 'Table'[CompletedDate] <= _selcmdate
                    && 'Table'[CompletedDate] > _preclean
                    && 'Table'[CompletedDate] < _nextclean
            )
        )
    )

Best Regards

[SherryZhao]

Dear Rena,

It works!!! Thanks for your support and I will try to understand the logic and coding.

Thanks you all again.

Best regards,

Sherry.