topic Re: Financial Balanse from Transactions showing the delta's only, but in need of totals in DAX Commands and Tips

Financial Balanse from Transactions showing the delta's only, but in need of totals

digialternative — Mon, 08 May 2023 15:08:24 GMT

Hello experts,

The following question must be simple to answer, but I fail in getting the answer, so please guide me into the correct direction.

I am building a financial dashboard, P&L, Balance, Cashflow etc.

As input I have a transaction table, and report layout and date based on month selection (Starting balanse 31-12-yyyy is value 00, month Jan = 01 etc.

Calculating the Balance is done using DAX by summing the indiviual Ledgers and that is working fine. However I end up with the month delta values (the change in the balance) while I need to add the starting balance (for January), add (starting balance + January delta) for february etc. But I fail to do something simple of adding the value of previous column to my current value.

On Internet I find all kinds of DAX examples, like the following:

Balance rolling=
VAR cmonth = CALCULATE(VALUES('Date'[Date]))
VAR pmonth = CALCULATE(VALUES('Date'[Date]), PREVIOUSMONTH(Date[Date]))

VAR stapshotactualcmonth = CALCULATE([Balance Total], all('Date'), 'Date'[Date] = cmonth)
VAR stapshotactualpmonth = CALCULATE([Balance Total], all('Date'), 'Date'[Date] = pmonth)

RETURN (stapshotactualcmonth + stapshotactualpmonth)

The error that I get returned is:

A table of multiple values was supplied where a single value was expected

This is where I get lost...

This is what I want/need:

The DAX for coming to these financials is:

//Sum of the transactions times a negative sign corrector
Balans SUM = SUM(Transacties[Transactie]) * [Balans Operator]

//Sum of the ledger range values
Balans Subtotaal =
VAR CurrentFrom = SELECTEDVALUE('Balance_Report'[FROM])
VAR CurrentTo = SELECTEDVALUE('Balance_Report'[TO])
Return

CALCULATE(
[Balans SUM],
FILTER(
ALL('Ledger_Mapping'),
'Ledger_Mapping'[Primary_Key] >= CurrentFrom &&
'Leger_Mapping'[Primary_Key] <= CurrentTo
)
)

// Values shown in the table
Balance Total = COALESCE([Balans SUM], [Balans Subtotaal])

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

Muhammad110 — Mon, 08 May 2023 16:06:27 GMT

It seems like you are trying to calculate the financial balance from transactions and display the delta values for each month. So you need to show the cumulative totals by adding the previous balance to the current value.

Balance rolling =
VAR cmonth = CALCULATE(MAX('Date'[Date]))
VAR pmonth = CALCULATE(MAX('Date'[Date]), PREVIOUSMONTH('Date'[Date]))

VAR stapshotactualcmonth = CALCULATE([Balance Total], 'Date'[Date] = cmonth)
VAR stapshotactualpmonth = CALCULATE([Balance Total], 'Date'[Date] = pmonth)

RETURN stapshotactualcmonth + IF(ISBLANK(stapshotactualpmonth), 0, stapshotactualpmonth)

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

digialternative — Mon, 08 May 2023 17:38:48 GMT

Thanks Muhammad10,

When I use your formula, I get only values for the first column (Opening balance), and values for stapshotactualcmonth. The stapshotactualpmonth remain 0. So somethings is not right yet...

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

Ajendra — Mon, 08 May 2023 18:39:43 GMT

Hello,

Please show some demo data alongwth the desired output.

Thanks!!

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

digialternative — Tue, 09 May 2023 06:02:35 GMT

Hello Muhammad10,

After playing with your setup, I found a working solution.

For the cmonth and pmonth, it works when I use the month field i.s.o date field (selecting 1 date vs an entire month)

Balans Rolling =

VAR cmonth = CALCULATE(MAX('Date'[Month]))

VAR pmonth = CALCULATE(MAX('Date'[Month]), PREVIOUSMONTH(Date[Date]))

var snapshotactualcmonth = CALCULATE(Balance_Report[Balans SUM], 'Datum'[Month]=cmonth)

var snapshotactualpmonth = CALCULATE(Balance_Report[Balans SUM], 'Datum'[Month]=pmonth)

RETURN snapshotactualcmonth + IF(ISBLANK(snapshotactualpmonth),0,snapshotactualpmonth)

Thanks for your help!!

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

Muhammad110 — Tue, 09 May 2023 08:59:37 GMT

Nice Bro sorry I was not able to reply early since I was out for a day glad you work on the solution always available

Regards

Re: Financial Balanse from Transactions showing the delta's only, but in need of totals

digialternative — Tue, 09 May 2023 17:57:25 GMT

Hello Muhammad110,
After a closer look, there is still an issue...
It seems to do the calculation nicely for the first 2 column (time period), It is summing the values. But for the 3rd time period, it takes the last to column values i.s.o. the last 3 values etc.

So see below, the Balans SUM = SUM('Transactions'[Value]) table.
For Crediteuren, a value of 9600 in Jan, 4800 in Feb, 9600 in March etc.

When I apply the formula:

Balans SUM Rolling =

VAR cmonth = CALCULATE(MAX(Datum[Maand]))

VAR pmonth = CALCULATE(MAX(Datum[Maand]), PREVIOUSMONTH(Datum[Datum]))

var snapshotactualcmonth = CALCULATE([Balans SUM], 'Datum'[Maand]=cmonth)

var snapshotactualpmonth = CALCULATE([Balans SUM], 'Datum'[Maand]=pmonth)

RETURN snapshotactualcmonth + IF(ISBLANK(snapshotactualpmonth),0,snapshotactualpmonth)

The result is not what I expected.

For Jan, the value is 9600 which is good.

For Feb, the value is 14400 (which is JAN + FEB (9600 + 4800)) which is good.
For Mar, the value is again 14400, while it should be (9600 + 4800 + 9600).

It seems that only the last 2 columns are added up, i.s.o. the real rolling SUM.

Can you explain this behavior?
It must be something really simple, but I am missing the way to do a Rolling calculation...