Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Very simple calculation question

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?

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

     

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        sirlanceohlott
        Advocate III
        4 years ago

        Anonymous 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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.