add value on row above on dashboard

[andywil456]

I'm trying to create a visual that will show a running balance. Here is a screenshot summarizing the data showing the balance for each category, but i really cannot find a solution how to create a running balance. 

I have tried creating a table and using the following fomula such as 

cashflow = CALCULATE(sum('cust and supplier cashflow'[Amount_1]),FILTER('cust and supplier cashflow',CONTAINS('master payment category','master payment category'[index],'cust and supplier cashflow'[index]))).

This only puts the total amount of £69117.74 on each payment category line. 

[andywil456]

Thank you @Habib and @v-haibl-msft,

I have now got this to work, i was creating this as a new column and not as a measure.

However it has brought me to another query/issue.

I have set up a monthly slicer which, the 'Amount 2' column will change as per my selection, however the 'Rolling Balance' measure will not change. Is there anyway of making this change with my slicer/ chiclet slicer selection?

[Habib]

What formula you have applied. It should work without any issue for any slicer.

[andywil456]

rolling balance = CALCULATE(SUM('total cashflow'[Amount 2]), FILTER(ALL('total cashflow'),'total cashflow'[index] <= MAX('total cashflow'[index])))

[Habib]

Don't use filter in your formula. please try to use the formula which I shared earlier. 

[andywil456]

Hi @Habib,

I have applied your formula

Measure 2 = CALCULATE(SUM('total cashflow'[Amount 2]),(ALL('customer payment data'),'customer payment data'[index]<= EARLIER('customer payment data'[index])))

but i'm getting the following error message

A single value for column 'index' in table 'customer payment data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

My total cashflow table has got mutliple lines per index category.

When i apply your formaula to my overivew table 

measure = CALCULATE(SUM(Table1[total amount]),ALL(Table1),Table1[Index]<= EARLIER(Table1[Index]))

I get the following error message

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[Habib]

Are you adding new measure or new column. It should be new column.

[andywil456]

I was initially adding it has a new measure, i have now applied the formula as a column but getting the following result

and when i use the cashflow table amount, it's not adding the previous balance

[v-haibl-msft · Accepted solution]

@andywil456

Please refer to below screenshot for relationships between the two tables I set up. My index values are numbers.

I’ve shared my Power BI file here. You can take a look at it if needed.

Best Regards,

Herbert

[v-haibl-msft]

@andywil456

It should work with following formula.

Running Total = 
CALCULATE (
    SUM ( 'cust and supplier cashflow'[Amount_1] ),
    FILTER (
        ALL('master payment category'),
        'master payment category'[index] <= MAX ( 'master payment category'[index] )
    )
)

Best Regards,

Herbert

[andywil456]

Hi @v-haibl-msft,

I'm still trying to apply you formula, on the example you have create how are you're relationships between the two tables set up? and are your index values as numbers or text? 

[andywil456]

Hi @v-haibl-msft,

I'm still trying to apply you formula, on the example you have create how are you're relationships between the two tables set up? and are your index values as numbers or text? 

[andywil456]

Thank you @v-haibl-msft,

I have applied your formula but i'm still not getting the correct result.

[Habib]

@andywil456 I have tried to produce data like yours and applied below formula. Only one change I have used table name CustCashFlow

RunningTotal = CALCULATE(SUM(CustCashFlow[amount_1]),ALL(CustCashFlow),CustCashFlow[Index]<=EARLIER(CustCashFlow[Index]))

It worked for me. Please refer to below.

[Habib]

First you need give sorting order for your categories in separate column and it should be a nubmer. Then you should use CALCULATE function. Somethig like below. Here PaymentCategoryOrdNo is new ordering Column

RunningTotal = CALCULATE(SUM(amount),all(Payment Category), PaymentCategoryOrdNo <=PaymentCategoryOrdNo ))

You can find help on below thread as well

http://community.powerbi.com/t5/Desktop/Running-Total/m-p/51014#M20441

[andywil456]

@Habib,

I have applied your calculation but i'm not sure what i have done wrong, its not giving the desired output. 

Close up on the formula and running balance

[Habib]

Hi @andywil456 There was a mistake in syntax. Apologies for that.

Please refer to below scenario.... I have created data set as below

OrdNo	Payment Category	Amount
1	Prepayment	450
2	Immediate	300
3	30 Days from Invoice	900
4	45 Days from Invoice	590

I added new column with following formula

RunningTotal = CALCULATE(SUM(Data[Amount]),ALL(Data), Data[OrdNo] <= EARLIER(Data[OrdNo]))

and it worked now.