Forum Discussion

BCAdmin's avatar
BCAdmin
New Member
4 years ago
Solved

Loop calculation

Hi All,

 

I'm trying to do this on Power BI, but I can't figure out the formula to do this. I want to use the previous calculated value from Column E to the next row and so on. Is it possible to be done on Power BI?

 

Below is the formula on Excel:

 

The data value:

 

Thank you!

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi BCAdmin ,

     

    I have a test on amitchandak's code and I find that I won't return the result you want. Firstly, we need to add an Index column in Power Query if you only have month column in text format.

    Your table will be like as below.

    Then add calculated columns in this table.

    Column4 = 
VAR _START =
    CALCULATE ( SUM ( 'Table'[Column1] ), ALL ( 'Table' ) )
RETURN
    _START
        + SUMX (
            FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
            [Column2] - [Column3]
        )
    New Column1 = 
VAR _PREVIOUS_COLUMN4 =
    CALCULATE (
        SUM ( 'Table'[Column4] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( 'Table'[Column1] ), _PREVIOUS_COLUMN4, 'Table'[Column1] )

    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.

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    BCAdmin , Assume you have date or index column (add in power query)

     

    You can create a new column

    [Column1] - Sumx(filter(Table, [Date] <= earlier([date]) ), [Column2] -[Column3])

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BCAdmin ,

     

    I have a test on amitchandak's code and I find that I won't return the result you want. Firstly, we need to add an Index column in Power Query if you only have month column in text format.

    Your table will be like as below.

    Then add calculated columns in this table.

    Column4 = 
VAR _START =
    CALCULATE ( SUM ( 'Table'[Column1] ), ALL ( 'Table' ) )
RETURN
    _START
        + SUMX (
            FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) ),
            [Column2] - [Column3]
        )
    New Column1 = 
VAR _PREVIOUS_COLUMN4 =
    CALCULATE (
        SUM ( 'Table'[Column4] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( ISBLANK ( 'Table'[Column1] ), _PREVIOUS_COLUMN4, 'Table'[Column1] )

    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.