Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
digialternative
Frequent Visitor

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

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:
Desired resultDesired result

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])

 

 

 

 

1 ACCEPTED SOLUTION

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!!

View solution in original post

6 REPLIES 6
Muhammad110
Helper I
Helper I

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

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.

 

Dashboard4.png

 

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.
 
Dashboard5.png
 
Can you explain this behavior?
It must be something really simple, but I am missing the way to do a Rolling calculation...
Ajendra
Resolver I
Resolver I

Hello,

 

Please show some demo data alongwth the desired output.

 

Thanks!!

Muhammad110
Helper I
Helper I

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)

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!!

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...

 

Dashboard2.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors