Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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
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.
@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
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |