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

[pmmarinou]

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

[AntrikshSharma]

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

[pmmarinou]

---

 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?

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!

[AntrikshSharma · Accepted solution]

@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?

[pmmarinou]

Using 

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

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