calculate total based on value of previous record / rown

[Anonymous]

Hi there, 

I need help to replicate something similar as below (done in excel). 

My table is formatted as above, except the last column. This is the one I'm trying to find a solution for. 

the issue is that there are 2 different logics in play to calculate the 'end value' per row. 

1: for the row, equal to current week of the year (in this example "50"), the calculation is straightforward, i.e. start value + "plus" - "minus" = end value. 

2: for each subsequent week, the starting value = the end value of the previous week. so the end value of all rows with a week later than current week = "end value of previous week" + "plus" - "minus". 

etc etc. 

so, for product "a" and week 50,

the end value is 5 + 0 - 1 = 4

and for weeks 51, 52, 1 etc :

4 + 2 - 1 = 5

5 + 1 - 1 = 5

5 + 0 - 5 = 0

0 + 15 - 5 =10

anyone with a great idea - or not so great, but equally getting results - please share!

thanks!

[mwegener]

Hi @Anonymous,

has your question been answered?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Nathaniel_C]

Hi @Anonymous ,

Try this. Note that WEEKNUM() you have to choose which day of the week your week begins.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

End Value Calced =
VAR _curYear =
    YEAR ( TODAY () )
VAR _curWeek =
    WEEKNUM ( TODAY (), 1 )//Choose which day of the week it starts
VAR _rowYear =
    MAX ( myTable[Year] )
VAR _rowWeek =
    MAX ( myTable[Week] )
VAR _useFirstCalc =
    IF ( _rowYear = _curYear && _rowWeek = _curWeek, 1, 0 )
VAR _curIndex =
    MAX ( myTable[Index] )
VAR _prevWeekEndBal =
    CALCULATE (
        MAX ( myTable[End value] ),
        FILTER ( ALLEXCEPT ( myTable, myTable[Product] ), myTable[Index] < _curIndex )
    )
RETURN
    IF (
        _useFirstCalc,
        MAX ( myTable[Start value] ) + MAX ( myTable[plus] )
            - MAX ( myTable[minus] ),
        _prevWeekEndBal + MAX ( myTable[plus] )
            - MAX ( myTable[minus] )
    )

[mwegener]

Hi @Anonymous 

calculate the change and put a running total over year and week.

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[kentyler]

This is one of the "Traps" of Power BI. Its forumlas look like Excel...but it doesn't work like Excel at all. Power BI has no concept of the "previous row" inside of its formulas, but you can reference that row by taking a couple of steps.

First you have to add a sequential index column. You should be able to do that in the query editor.

Then you use the index column to "move back", your formula gets the index for the row it is in the context of, then subtracts 1 and uses that value to lookup the "previous" row. Here is a code example.

PrevValue =
VAR previndex =
    VALUES ( Table1[Index] ) - 1
RETURN
    CALCULATE ( SUM ( Table1[Value] ) )
        - CALCULATE (
            SUM ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Index] = previndex )
        )

The VALUES() function is necessary because, inside a measure, there is no row context, so you can't just write Table1[Index] like you could in a calculated column.... since this measure is probably being placed in a table or matrix, it does have a context from the table or matrix that contains only the current row... so VALUES() has only one value to return.

The ALL() function wrapped around the table in the FILTER is to remove any external filter context that might otherwise be limiting the rows available from Table1
I'm a personal Power BI trainer.  I learn something new every time I answer a question.