topic Re: Faster way to calculate total balance as at the end of a year/current date in DAX Commands and Tips

Faster way to calculate total balance as at the end of a year/current date

pmmarinou — Sat, 05 Oct 2024 09:43:46 GMT

Dear Community,

I want to display the total of a column cummulative for each year. For example to calculate the value of 2023 and 2024 in the screenshot below:

I use the following DAX, which give me the correct result.

Value =

CALCULATE(

SUM(Transactions[Amount]),

FILTER(

ALL(DateTable),

DateTable[Date] <=

IF(

YEAR(MAX(DateTable[Date])) = MAX(DateTable[Year]),

MAX(DateTable[Date]),

DATE(MAX(DateTable[Year]), 12, 31)

)

However, when I show all of the investments transactions (around 15.000 rows) the report page is a bit slow when I filter or browse the matrix. Any suggestions to get the same result but faster meaning by doing less computations?

Thank you all in advance

Re: Faster way to calculate total balance as at the end of a year/current date

AntrikshSharma — Sat, 05 Oct 2024 11:43:08 GMT

pmmarinou This part

YEAR(MAX(DateTable[Date])) = MAX(DateTable[Year])

of your code is always going to evaluate to TRUE because both are fetching the Date and Year of the same year from the filter context, are you assuming it is referencing to row context created by FILTER?

To make it faster you can first pre compute the Amount with the help of

ADDCOLUMNS ( ALL ( DateTable[Date] ), "Amount", [Amount] )

construct and then FILTER it accordingly, use variables to segregate the code into chunks.

Re: Faster way to calculate total balance as at the end of a year/current date

pmmarinou — Sat, 05 Oct 2024 16:21:52 GMT

YEAR(MAX(DateTable[Date])) = MAX(DateTable[Year])

Ok I edited it like this and it seems to work:

Value=

CALCULATE(

SUM(Transactions[Amount]),

FILTER(

ALL(DateTable),

DateTable[Date] <= MAX(DateTable[Date])

))

To make it faster you can first pre compute the Amount with the help of

ADDCOLUMNS ( ALL ( DateTable[Date] ), "Amount", [Amount] )

construct and then FILTER it accordingly, use variables to segregate the code into chunks.

The DataTable and the Transaction Table are different. Can I return a new table from two tables using this function?

Thank you for your reply and help!

Re: Faster way to calculate total balance as at the end of a year/current date

AntrikshSharma — Sat, 05 Oct 2024 16:39:13 GMT

pmmarinou This will be a measure, here is a generic pattern:

VAR DailyAmount = ADDCOLUMNS ( ALL ( DateTable[Date] ), "@Amount", [Amount] ) VAR LastVisibleDate = MAX ( DateTable[Date] ) VAR PreviousDates = FILTER ( DailyAmount, DateTable[Date] <= LastVisibleDate ) VAR RunningTotal = SUMX ( PreviousDates, [@Amount] ) RETURN Result

See if this works?

Re: Faster way to calculate total balance as at the end of a year/current date

pmmarinou — Tue, 22 Oct 2024 06:31:39 GMT

Using

ADDCOLUMNS ( 
		ALL ( DateTable[Date] ), 
		"@Amount", [Amount] 
	)

I lose all the other columns included in my transaction table needed for filtering