most recent value of same calculated column previous row using Variable

[surajde]

Hi All, 

Please help with a formula for getting most recent value of same calculated column previous row. 

I need to replicate this in PowerBI. 
Column E is calculated column and I need to somehow capture the last calculated value on a variable and then variable +1 

Excel formula : 

=IF(A2<>A1, 0, IF(C2=0, E1, E1+1))

Data : 

ID	Delta	Time	Current Group Time	Count
123123	0.00	0	7/1/22 9:04 AM	0
123123	0.00	40.966667	7/1/22 9:04 AM	1
234243	0.00	0	7/1/22 9:04 AM	0
234243	0.00	0	7/1/22 9:04 AM	0
234243	0.00	38.933333	7/4/22 5:46 AM	1
234243	74.88	1	7/4/22 6:43 AM	2
234243	0.00	38.933333	7/4/22 6:43 AM	3
345675	0.00	38.933333	7/4/22 6:43 AM	0

[amitchandak · Accepted solution]

@surajde , Create a new column like

if([Time] =0, 0, countx(filter(Table, [ID] = earlier([ID]) && [Time] >0 && [Current Group Time] <= earlier([Current Group Time])), [ID]) )

[v-yanjiang-msft · Accepted solution]

Hi @surajde ,

According to your description, here's my solution.

1.Add an index column in Power Query.

2.Create two calculated columns.

flag =
IF (
    'Table'[ID]
        <> MAXX (
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
            'Table'[ID]
        ),
    "yes",
    "no"
)

Column =
VAR _c = 'Table'[Index]
RETURN
    IF (
        [ID]
            <> MAXX (
                FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
                'Table'[ID]
            ),
        0,
        IF (
            MAXX (
                FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
                'Table'[flag]
            ) = "no",
            IF (
                'Table'[Time] = 0,
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Index] < EARLIER ( 'Table'[Index] )
                            && 'Table'[Index]
                                > MAXX (
                                    FILTER ( 'Table', 'Table'[flag] = "yes" && 'Table'[Index] < _c ),
                                    'Table'[Index]
                                )
                            && 'Table'[Time] <> 0
                    )
                ) + 0,
                COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Index] < EARLIER ( 'Table'[Index] )
                            && 'Table'[Index]
                                > MAXX (
                                    FILTER ( 'Table', 'Table'[flag] = "yes" && 'Table'[Index] < _c ),
                                    'Table'[Index]
                                )
                            && 'Table'[Time] <> 0
                    )
                ) + 1
            ),
            IF ( 'Table'[Time] = 0, 0, 1 )
        )
    )

Get the correct result.

I also change the data source with different IDs, still get correct result.

I attach my file below for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Community Support Team_yanjiang

[surajde]

 Amazing!! This one worked 😀

[amitchandak · Accepted solution]

@surajde , Create a new column like

if([Time] =0, 0, countx(filter(Table, [ID] = earlier([ID]) && [Time] >0 && [Current Group Time] <= earlier([Current Group Time])), [ID]) )

[surajde]

@amitchandak , thanks for quick response. 

Please check that wehn I add your formula to BI then I get below results. most of the values are 0. 

Also we're not complaring time here. your formula is comparing previous and current time. 

Please try with below data: in this data last column is taking its own last calculated value. 

ID	CREATE_DATE	MODIFY_DATE	Delta	Scope	Time	Current Group Time	Count
4221757	7/1/22 9:04 AM	7/1/22 9:04 AM	0	YES	0	7/1/22 9:04 AM	0
4221757	7/1/22 9:04 AM	7/1/22 9:08 AM	0	YES	0	7/1/22 9:04 AM	0
4221757	7/1/22 9:04 AM	7/1/22 9:45 AM	0	YES	40.96666667	7/1/22 9:04 AM	1
4221757	7/1/22 9:04 AM	7/1/22 2:01 PM	0	NO	0	7/1/22 2:01 PM	1
4221757	7/1/22 9:04 AM	7/1/22 2:07 PM	6.46666667	NO	0	7/1/22 2:01 PM	1
4221757	7/1/22 9:04 AM	7/4/22 5:45 AM	0	NO	0	7/1/22 2:01 PM	1
4221757	7/1/22 9:04 AM	7/4/22 5:46 AM	0	YES	0	7/4/22 5:46 AM	1
4221757	7/1/22 9:04 AM	7/4/22 6:25 AM	0	YES	38.93333333	7/4/22 5:46 AM	2
4221757	7/1/22 9:04 AM	7/4/22 6:27 AM	0	YES	0	7/4/22 5:46 AM	2
4221757	7/1/22 9:04 AM	7/4/22 6:31 AM	0	YES	0	7/4/22 5:46 AM	2
4221757	7/1/22 9:04 AM	7/4/22 6:43 AM	0	NO	0	7/4/22 6:43 AM	2
4221757	7/1/22 9:04 AM	7/4/22 7:58 AM	74.88333333	1	0	7/4/22 6:43 AM	2
4221757	7/1/22 9:04 AM	7/6/22 2:50 AM	0	NO	0	7/4/22 6:43 AM	2
4221757	7/1/22 9:04 AM	7/6/22 3:02 AM	0	YES	0	7/6/22 3:02 AM	2
4221757	7/1/22 9:04 AM	7/6/22 3:07 AM	0	YES	4.76666667	7/6/22 3:02 AM	3
4221757	7/1/22 9:04 AM	7/6/22 3:26 AM	0	NO	0	7/6/22 3:26 AM	3