Cumulative (Running) total Balance sheet (year-over-year)

[HB2BI]

Hi all!

I'm struggling to find a fitting solution for calculating a running total on my balance sheet.

For this I need to sum the first year (i.e. 2013), which wasn't that hard.

Then for 2014 a running total has to be calculated that consists of:

 - Total year end 2013;
 - All transactions in 2014.

Again, not that hard using sameperiodlastyear.

From 2015 forward, i can't get it to work.

Either it won't respect the rows (categories) I have made, or it calculates everything up to today and thus disregards the year columns.

At this moment i have the following:

Where SumBALTotals are the transactions for that year, and CumSumBal should be the running total.

For SumBalTotals I use:

SumBALTotals = 
ROUND(
CALCULATE(
    SUM('DATA AlleSaldi'[Amount]);
    'Saldi BG'[BalanceType] in { "B" } );
    0)

And for CumSumBal I use:

CumSumBal = 
CALCULATE(
    sum('DATA AlleSaldi'[Amount]);
    FILTER(
        ALLSELECTED('DATA AlleSaldi');
        'DATA AlleSaldi'[Datum] <= max( 'DATA AlleSaldi'[Datum])
    )
)

I have tried numerous solutions from different fora, but I just can't get it to work.

So. Anyone?

[v-frfei-msft]

Hi @HB2BI ,

How about changing the formula to :

CumSumBal = 
CALCULATE(
    sum('DATA AlleSaldi'[Amount]);
    FILTER(
        ALLSELECTED('DATA AlleSaldi');
        'DATA AlleSaldi'[Year] <= max( 'DATA AlleSaldi'[Year])
    )
)

If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

[amitchandak]

@HB2BI , If you want new cumulative  every year then use datesytd and totalmtd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))
Cumm Days = CALCULATE(distinctcount(date[date]),filter(date,date[date] <=max(Sales[Sales Date])))

[HB2BI]

Thanks for the reply @amitchandak , but unfortunately it still doesn't give me a running total.

It returns the same values that are already in the year total column (SumBalTotals).

Perhaps some more clarification;

in 2013, the values are good (all transactions during 2013).

in 2014 I need the values from 2014 SumBalTotals + 2013 SumbalTotals

in 2015 I need the outcome of the 2014 formula + 2015 SumBalTotals.

and so forth..

[amitchandak]

@HB2BI ,Can you share sample data and sample output.