Forum Discussion

nasbkrv's avatar
nasbkrv
Regular Visitor
6 years ago
Solved

Previous row value formula

Hi Team, I read this post: https://community.powerbi.com/t5/Desktop/Calculated-column-From-To-formula/td-p/1274376 and it works for me too, but not for every row. The given example is: And...
Need Help
Show and Tell
  • Icey's avatar
    Icey
    6 years ago

    Hi nasbkrv ,

     

    Please add an Index column in Power Query Editor first. And then, create calculated columns like so:

    From =
CALCULATE (
    SUM ( 'Table'[Metric] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Index] < EARLIER ( 'Table'[Index] )
    )
) + 0

    To =
'Table'[From] + 'Table'[Metric]

     

    Best Regards,

    Icey

     

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

Icey's avatar
Icey
Community Support
6 years ago

Hi nasbkrv ,

 

Please add an Index column in Power Query Editor first. And then, create calculated columns like so:

From =
CALCULATE (
    SUM ( 'Table'[Metric] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[Index] < EARLIER ( 'Table'[Index] )
    )
) + 0

To =
'Table'[From] + 'Table'[Metric]

 

Best Regards,

Icey

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Icey ,

    I'm testing the suggested formula but with a larger file and the system gives me an error that the file is too big and there is no enough memory. Any ideas if there is another formula or function except earlier which gives the same result?

    Thank you.