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 : IDDeltaTimeCurrent Group TimeCount1231230.0007/1/22 9:04 AM01231230.0040.9666677/1/22 9:04 AM12342430.0007/1/22 9:04 AM02342430.0007/1/22 9:04 AM02342430.0038.9333337/4/22 5:46 AM123424374.8817/4/22 6:43 AM22342430.0038.9333337/4/22 6:43 AM33456750.0038.9333337/4/22 6:43 AM0

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

Helper I

3 years ago

Solved

most recent value of same calculated column previous row using Variable

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

Need Help

Show and Tell

Tips and Tricks

amitchandak
3 years ago
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

3 years ago

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

most recent value of same calculated column previous row using Variable.pbix28 KB

4 Replies

amitchandak

Super User

3 years ago

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

Helper I

3 years ago

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

v-yanjiang-msft

Community Support

3 years ago

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

most recent value of same calculated column previous row using Variable.pbix28 KB

surajde
Helper I
3 years ago
Amazing!! This one worked 😀

Forum Discussion

most recent value of same calculated column previous row using Variable

4 Replies

Related content

Dynamic Variable in Calculated Columns

Understanding variable calculation

Facing challenge to calculate previous year QTD

Previous Month's MTD calculation

Slow visual calculation (PREVIOUS and RUNNINGSUM)