Martin Pugsley
Martin Pugsley

Reputation: 65

Bring previous day value as starting point for next day in a measure within Power BI

I have been trying for a few days to do this with no avail and its probably something simple.

enter image description here

I am trying to calculate the PRED_BL column which is based on PRED_WORKLOAD + PRED_REC - WORKDONE

As you can see I can do the first day because I have the PRED_WORKLOAD volume for the 04/10/2021, but PRED_WORKLOAD future dates are blank - what I need to do is bring the 314419 from the 4ths PRED_BL column as the PRED_WORKLOAD value for the 5th October, to start the calculation but also make sure the calculated PRED_BL's are used as the starting points for each of the future dates.

This is probably something simple but for a Power BI newbie like me not so.

Hope someone can help me, thanks in advance

Upvotes: 2

Views: 642

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40304

As in this similar post, you can't recursively define columns based on previous ones but you can compute cumulative totals instead to a similar effect.

It would look something like this:

PRED_BL =
SUMX (
    FILTER ( Table1[DATE], Table1[DATE] <= MAX ( Table1[DATE] ) ),
    Table1[PRED_WORKLOAD] + Table1[PRED_REC] - Table1[WORKDONE]
)

Upvotes: 1

Related Questions