Add up previous row value in a new Week Row

[Rigolleto]

Hi

I need help to populate the POSQTY column, as you can see in the attached picture the POSQTY has values from 202220 to 202231, Do I need to do is to add the value for wweek 202231 + the Average_Sales column and then populate the week 202232, for week 202233 I need to take the previous value, for 202232 + the Average_Sales  and populate for week 202233, and so for for the rest of weeks, any body can help me with some logic?

I really appreciate your help

[Anonymous · Accepted solution]

Hi @Rigolleto ,

Please try to  create a measure with below dax formula and add it to table visual.

Measure =
VAR _a =
    MAX ( 'Table'[POSQTY] )
VAR _b =
    CALCULATE ( MAX ( [Index] ), FILTER ( ALL ( 'Table' ), [POSQTY] <> 0 ) )
VAR _c =
    CALCULATE ( MAX ( 'Table'[POSQTY] ), FILTER ( ALL ( 'Table' ), [Index] = _b ) )
VAR _d =
    CALCULATE (
        SUM ( 'Table'[Average_Sales] ),
        FILTER ( ALL ( 'Table' ), [Index] >= _b && [Index] < MAX ( 'Table'[Index] ) )
    )
RETURN
    IF ( MAX ( [Index] ) <> 1 && _a = 0, _c + _d, MAX ( 'Table'[POSQTY] ) )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.