Very simple calculation question

[kressb]

I have one Table with 3 columns in PowerBI. I am trying to make a calculated column - "PrevNumber"

PrevUniqueID will always have a match in the UniqueID column of a different Row.

Goal is to pull the Number column/value for whichever Row has a UniqueID that matches the PrevUniqueID.

I am trying this formula:

CALCULATE(

sum(Table[Number]),

Filter(Table,Table[PrevUniqueID]=Earlier(Table[UniqueID]))

There are no other tables or columns. Just this. 

I'm not getting the correct answer. This is what was returned:

Any ideas?

[v-rzhou-msft · Accepted solution]

Hi @kressb ,

Try this code to create a calculated column.

PreviousMonthNumber =
VAR _PreMonthUniqueID =
    CALCULATE (
        SUM ( 'Table'[Number] ),
        FILTER ( 'Table', 'Table'[UniqueID] = EARLIER ( 'Table'[PreMonthUniqueID] ) )
    )
RETURN
    _PreMonthUniqueID + 0

Result is as below.

Best Regards,
Rico Zhou

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

[sirlanceohlott]

Hey there, 

EnterpriseDNA has a great writeup on how to achieve this, you can check it out here: https://forum.enterprisedna.co/t/previous-row-value/9789

[kressb]

@sirlanceohlott thanks for the response!
I had been thinking maybe I need indexing.

I thought I could get around it if I could just figure out why the formula is essentially pulling backwards (Dec 2021 is pulling from Jan 2022, instead of having Jan 2022 pulling from Dec 2021). but maybe indexing is really the only answer.. 

[sirlanceohlott]

@kressb you're welcome! 

Yeah, I know it's a little frustrating, but I believe the indexing will help with what you are trying to achieve. I've had similar challenges where indexing was one of the ways to get it functioning as I intended.

Best of luck as you continue on, others might have some different solutions too!