Forum Discussion

Robin96's avatar
Robin96
Helper II
2 years ago

Running total problem

i have a switch case that is showing different values for each row in a matrix

i have a table that shows the income, expenses and my cashflow per week.

This is how the matrix should show values:
Row 1: show cashflow
Row 2: show net cashflow this week + previous week balance
row 3 and further: show net cashflow this week + the values from row 2

i have problem with row 3 and further.

This is the code i have now:

Cash balance =
var min_ = MIN('date'[year_week])

var cash_Balance =
CALCULATE(SUM(Manual_posts[amount]))

var cash_flow =
CALCULATE([Net cashflow])

VAR forrige_uke_cashflow =
CALCULATE([Net cashflow],FILTER(ALL('date'),[year_week]=SELECTEDVALUE('date'[year_week])-1))

VAR forrige_uke_cashbalance =
CALCULATE(cash_Balance,FILTER(ALL('date'),[year_week]=SELECTEDVALUE('date'[year_week])-1))

var calc_ = cash_flow - forrige_uke_cashflow

var net_cash_forrige_uke_balance =
[test (forrige uke cash balance)] + cash_flow

Var shadow_2 =
CALCULATE([TEST_cash balance_shadow2],FILTER(ALL('date'),[year_week]=SELECTEDVALUE('date'[year_week])-2))

var shadow =
[TEST_cash balance_shadow] + [Net cashflow]

var shadow2 =
shadow_2

VAR SWITCH_ =
SWITCH(
    TRUE(),
    SELECTEDVALUE('date'[year_week]) = calculate(MIN('date'[year_week]), ALLSELECTED('Date'[Week])),cash_Balance,
    SELECTEDVALUE('date'[year_week]) = calculate(MIN('date'[year_week])+1, ALLSELECTED('Date'[Week])),net_cash_forrige_uke_balance,
    SELECTEDVALUE('date'[year_week]) > calculate(MIN('date'[year_week])+1, ALLSELECTED('Date'[Week])),shadow,
    9
    )
RETURN SWITCH_


How can i make the third row calculate this weeks net cash and subsctract the last weeks balance

my date table have weeknumber column. and the report uses week and year slicer.

Need Help

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    Robin96 Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Robin96's avatar
      Robin96
      Helper II
      2 years ago

      Greg_Deckler 

      This is the data i have as test


      This is the expected output i want:
      The orange fields is what i use for the the calculation, and the green field is where i want the result

       

      • JamesFR06's avatar
        JamesFR06
        Resolver IV
        2 years ago

        Hi

         

        Please try this

        Cashflow =
        VAR minweek =
        MIN ( RT[Week] )
        VAR Firstcash =
        CALCULATE ( MAX ( RT[Cash Balance] ), RT[Week] = minweek, ALL ( rt ) )
        VAR Activweek = RT[Week]
        VAR RTotal =
        CALCULATE (
        SUM ( RT[Net Cashflow] ),
        RT[Week] > minweek
        && RT[Week] <= Activweek,
        ALL ( RT )
        )
        VAR result =
        IF ( Activweek = minweek, Firstcash, RTotal + Firstcash )
        RETURN
        result