Cumulative Sum not calculating correctly when segmented by month

Question

I'm working with a dataset in Power BI which contains a table entitled "Transactions" which column showing revenue collected and the date of the transaction. Ultimately, we want to find the cumulative running total of those transactions.

Transaction Date	Amount
1/5/2023	$ 1,000
1/14/2023	$ 450
2/5/2023	$ (500)
2/22/2023	$ 1,453
2/22/2023	$ 220
5/1/2023	$ (2,000)
11/1/2023	$ 2,500

A relationship exists between this table and a table entitled "Calendar Table" which appears as follows:

Date	Year	Month	Start of Month
1/1/2023	2023	Sunday, January 1, 2023	January 2023
1/2/2023	2023	Monday, January 2, 2023	January 2023
1/3/2023	2023	Tuesday, January 3, 2023	January 2023

The table maintains this format for all 365 days of 2023.
The two tables have a relationship using the 'Transaction Date' column and the 'Date' column respectively.

What we are trying to accomplish is to create a Matrix chart which will display the cumulative total of all Amount transactions at the end of each Month. Currently, the Matrix table has 'Month' (from the Calendar Table) as the columns and 'Cumulative Amount' (DAX formula) as the Values.

So we would want to see something like this:

Thursday, January 5, 2023	Saturday, January 14, 2023	Friday, February 5, 2023	Wednesday, February 22, 2023
$ 1,000	$ 1,450	$ 950	$ 2,403

The DAX measure I am using for the Cumulative Amount is as follows:

Cumulative Amount =
CALCULATE( 'Transactions'[Amount],
FILTER(ALL('Transactions'[Transaction Date] ),
'Transactions'[Transaction Date] <= MAX(''Transactions'[Transaction Date]) ) )

When I apply this DAX in the values of the Matrix, I get the following result:

Thursday, January 5, 2023	Saturday, January 14, 2023	Friday, February 5, 2023	Wednesday, February 22, 2023
$ 1,000	$ 450	$ (500)	$ 1,673

Question: How can I get my DAX measure to calculate the cumulative total like we want? Thanks!

Answer 1

It is working with this DAX formula:

 Cumulative Amount = 
    CALCULATE(Sum( 'Transactions'[Amount]),
    FILTER(ALL('Transactions'[Transaction Date] ),
    'Transactions'[Transaction Date] <= MAX('Transactions'[Transaction Date]) ) )

Also, make sure both the date columns used in the data relationship are data type "Date" and not "DateTime".