How to get YTD values using string (text) columns in matrix

[Jensk]

I want to calculate my running total by period. What do I need to do to get it work when using text columns (PERIOD_SHORT) in my matrix?

PERIOD_SHORT is sorted by PERIOD_ID in my table, but it does not work with my DAX.

My Data Model (keys are TRXDATE <> Date):

My Measures:

Amount = SUMX(GL20000, GL20000[Amount])

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

Using PERIOD_ID as columns works because they can be ranked (year filter set to 2023):

Using PERIOD_SHORT does not work because it is a string(text)  (year filter set to 2023) :

My Data:

PERIOD_SHORT	Amount	TRXDATE	PERIOD_ID	Account
Open	100	01-01-2023	0	Account1
Jan	100	01-01-2023	1	Account1
Feb	100	01-02-2023	2	Account1
Mar	100	01-03-2023	3	Account1
Apr	100	01-04-2023	4	Account1
Maj	100	01-05-2023	5	Account1
Jun	100	01-06-2023	6	Account1
Jul	100	01-07-2023	7	Account1
Aug	100	01-08-2023	8	Account1
Sep	100	01-09-2023	9	Account1
Okt	100	01-10-2023	10	Account1
Nov	100	01-11-2023	11	Account1
Dec	100	01-12-2023	12	Account1
Open	200	01-01-2024	0	Account1
Jan	200	01-01-2024	1	Account1
Feb	200	01-02-2024	2	Account1
Mar	200	01-03-2024	3	Account1
Apr	200	01-04-2024	4	Account1
Maj	200	01-05-2024	5	Account1
Jun	200	01-06-2024	6	Account1
Jul	200	01-07-2024	7	Account1
Aug	200	01-08-2024	8	Account1
Sep	200	01-09-2024	9	Account1
Okt	200	01-10-2024	10	Account1
Nov	200	01-11-2024	11	Account1
Dec	200	01-12-2024	12	Account1

[Anonymous]

Hi @Jensk ,

You can try formula like below:

YTD Amount = 
VAR SelectedPeriod =
    SELECTEDVALUE ( 'YourMatrix'[PERIOD_SHORT] )
RETURN
    CALCULATE (
        SUM ( 'YourMatrix'[Amount] ),
        FILTER (
            ALL ( 'YourMatrix' ),
            'YourMatrix'[PERIOD_SHORT] <= SelectedPeriod
                && 'YourMatrix'[TRXDATE] <= MAX ( 'Date'[Date] )
        )
    )

Best Regards,
Adamk Kong

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

[Greg_Deckler · Accepted solution]

@Jensk Not sure if that will work with string text. Maybe if you have it sort by a numeric column (Sort by). You might also try this approach. Better Running Total - Microsoft Fabric Community