Forum Discussion

Rigolleto's avatar
Rigolleto
Icon for Resolver I rankResolver I
4 years ago
Solved

Add up previous row value in a new Week Row

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_S...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.