Calculating the SUM of the Value of the row above added to transaction value of own row

[Anonymous]

Hi!

I'm trying to create an overview of bank accounts and their final amount at the end of the day.

The information at hand is:

• DB tables that contain
• Transactions
• Transaction dates
• Bank account numbers
• EXCEL sheet that contain the starting value of the bank accounts at a certain date

The information from the DB is loaded by using the following SQL statement:

SELECT SUM([Remaining Amount])

, [G_L Account No_]
, [Posting Date]
FROM [NAV-LIVE].[dbo].[DGW Holding BV$G_L Entry]
WHERE ([G_L Account No_] = 1330 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1331 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1333 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1334 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1335 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1336 AND [Posting Date] >= '20141101')
OR ([G_L Account No_] = 1332 AND [Posting Date] >= '20141101')
GROUP BY [G_L Account No_], [Posting Date]
ORDER BY [G_L Account No_]

G_L Account No_ refers to the bank account, posting date is MIN 01-11-2014 (this is where our starting values in EXCEL begin)

The goal is as follows:
Enter the starting value in a new column named FinalAmount on the first row, after this the goal is to add the FinalAmount of row one to the transaction amount of row two as a result for FinalAmount row two, etc. 

Multiple Queries have already been tried, mostly with the same result: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

EXAMPLE we tried to add a date column to overcome the missing dates:

FullTable = ADDCOLUMNS(

CALENDAR(MIN('Bank Saldi'[Posting Date]), MAX('Bank Saldi'[Posting Date])) ,

"Quantity",

LOOKUPVALUE(

'Bank Saldi'[Transacties],

'Bank Saldi'[Posting Date],

MAXX(

FILTER('Bank Saldi', 'Bank Saldi'[Posting Date] <= EARLIER([Posting Date])),

[Posting Date])))

EXAMPLE we used to calculate the running total: 

EindSaldo =

VAR EindSaldo =

CALCULATE (

SUM ( 'Bank Saldi'[Transacties] ),

FILTER (

ALL ( 'Bank Saldi'[G_L Account No_] ),

'Bank Saldi'[G_L Account No_] = "1310" <-- this should not refer to just one bank account, but needs to make sure values of the same bank account are added))

VAR AlleTransacties =

CALCULATE (

SUM ( 'Bank Saldi'[Transacties] ),

FILTER (

ALL ( 'Bank Saldi'[Posting Date] ),

'Bank Saldi'[Posting Date] >= MAX ( 'Bank Saldi'[Posting Date] )))

RETURN

IF ( EindSaldo, AlleTransacties, EindSaldo )

If anyone can help, please!

[d_gosbell]

Trying to calculate this in a column in a tabular model is generally a bad idea. You would be creating a non-additive column and you are assuming a fixed order of rows which is not the case (the data is compressed and stored per column)

But you should be able to achieve the output you want using a cumulative measure (see https://www.daxpatterns.com/cumulative-total/)

[Anonymous]

Thanks! I've tried the cumulative measure already, but it still doesn't work, unfortunately.