Cumulative total in all fields in matrix with blanks

[Jensk]

I am trying to calculate running totals of financial transactions, but I need to calculate them in a matrix where I have periods as columns (months).

My raw data looks like this:

And my desired outcome is this:

For each column I want the cumulative sum of AMOUNT up until that period (PERIOD_ID), but it should be calculated for all fields including those with blanks.

My raw data:

PERIOD_ID	Amount	TRXDATE	Account
0	100	01-01-2024	Account1
1	100	01-01-2024	Account1
1	200	01-01-2024	Account2
2	100	01-02-2024	Account1
2	200	01-02-2024	Account3
3	100	01-03-2024	Account1
3	200	01-03-2024	Account2
4	100	01-04-2024	Account1
4	200	01-04-2024	Account3
5	100	01-05-2024	Account1
5	200	01-05-2024	Account2
6	100	01-06-2024	Account1
6	200	01-06-2024	Account3
7	100	01-07-2024	Account1
8	100	01-08-2024	Account1
8	200	01-08-2024	Account2
9	100	01-09-2024	Account1
9	200	01-09-2024	Account3
10	100	01-10-2024	Account1
11	100	01-11-2024	Account1
12	100	01-12-2024	Account1

I am using PERIOD_ID as columns in the matrix and ACCOUNT as rows.

My Measures:

Amount = SUMX(GL20000, GL20000[AMOUNT])

Amount YTD = 
CALCULATE(
    [Amount],
    GL20000[PERIOD_ID] <= MAX(GL20000[PERIOD_ID])
)

AmountYTD works when there are no blanks, but I don’t know how to get a value in the blank fields of the matrix when I add ACCOUNTS as rows:

When I add ACCOUNTS, I get blanks:

How do I solve this problem?

[Anonymous · Accepted solution]

Hi, @Jensk 

I used the sample data you provided:

The reason why empty fields cannot be accumulated is when the current Acount does not have a corresponding PERIOD_ID. So I created a new calculation table:

Use this one column of the calculated table in the matrix visual:

I use the following DAX expression to calculate YTD:

YTD =
VAR _currentid =
    SELECTEDVALUE ( 'Table 3'[PERIOD_ID] )
VAR _a =
    SELECTEDVALUE ( 'GL20000'[Account] )
VAR _YTDs =
    CALCULATE (
        [Sum of Amount],
        FILTER (
            ALL ( 'GL20000' ),
            'GL20000'[PERIOD_ID] <= _currentid
                && 'GL20000'[Account] = _a
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table 3'[PERIOD_ID] ),
        IF ( _YTDs = BLANK (), 0, _YTDs ),
        [Sum of Amount]
    )

The results are shown in the figure below:

I provide the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jensk]

Thank you so much! That is a huge help! You just made my weekend! 😁