Forum Discussion
Group Running total for date and type
Working on this, and i'm stuumped
I can get to group riunning toital by date, but not by Type/Date combined
Any help would be sorely appreciated
| Type | Date | Value |
| a | 1/1/2022 | 1 |
| a | 2/1/2022 | 2 |
| a | 3/1/2022 | 3 |
| a | 4/1/2022 | 4 |
| a | 5/1/2022 | 5 |
| a | 6/1/2022 | 6 |
| a | 7/1/2022 | 7 |
| a | 8/1/2022 | 8 |
| a | 9/1/2022 | 9 |
| a | 10/1/2022 | 10 |
| a | 11/1/2022 | 11 |
| a | 12/1/2022 | 12 |
| a | 1/1/2023 | 1 |
| a | 2/1/2023 | 2 |
| a | 3/1/2023 | 3 |
| a | 4/1/2023 | 4 |
| a | 5/1/2023 | 5 |
| a | 6/1/2023 | 6 |
| a | 7/1/2023 | 7 |
| a | 8/1/2023 | 8 |
| a | 9/1/2023 | 9 |
| a | 10/1/2023 | 10 |
| a | 11/1/2023 | 11 |
| a | 12/1/2023 | 12 |
| b | 1/1/2022 | 13 |
| b | 2/1/2022 | 14 |
| b | 3/1/2022 | 15 |
| b | 4/1/2022 | 16 |
| b | 5/1/2022 | 17 |
| b | 6/1/2022 | 18 |
| b | 7/1/2022 | 19 |
| b | 8/1/2022 | 20 |
| b | 9/1/2022 | 21 |
| b | 10/1/2022 | 22 |
| b | 11/1/2022 | 23 |
| b | 12/1/2022 | 24 |
| b | 1/1/2023 | 1 |
| b | 2/1/2023 | 2 |
| b | 3/1/2023 | 3 |
| b | 4/1/2023 | 4 |
| b | 5/1/2023 | 5 |
| b | 6/1/2023 | 6 |
| b | 7/1/2023 | 7 |
| b | 8/1/2023 | 8 |
| b | 9/1/2023 | 9 |
| b | 10/1/2023 | 10 |
| b | 11/1/2023 | 11 |
| b | 12/1/2023 | 12 |
Measure
ToDate=CALCULATE(
SUM(Table1[Value]),
filter( all(Table1), isonorafter(Table1[Date],max(Table1[Date]),DESC) ))
| Actual Output | |||||
| Type | Date (Year) | Date (Quarter) | Sum of Value | ToDate | |
| a | 2022 | Qtr1 | 6 | 48 | |
| a | 2022 | Qtr2 | 15 | 114 | |
| a | 2022 | Qtr3 | 24 | 198 | |
| a | 2022 | Qtr4 | 33 | 300 | |
| a | 2022 Total | 300 | |||
| a | 2023 | Qtr1 | 6 | 312 | |
| a | 2023 | Qtr2 | 15 | 342 | |
| a | 2023 | Qtr3 | 24 | 390 | |
| a | 2023 | Qtr4 | 33 | 456 | |
| a | 2023 Total | 456 | |||
| a Total | 156 | 456 | |||
| b | 2022 | Qtr1 | 42 | 48 | |
| b | 2022 | Qtr2 | 51 | 114 | |
| b | 2022 | Qtr3 | 60 | 198 | |
| b | 2022 | Qtr4 | 69 | 300 | |
| b | 2022 Total | 300 | |||
| b | 2023 | Qtr1 | 6 | 312 | |
| b | 2023 | Qtr2 | 15 | 342 | |
| b | 2023 | Qtr3 | 24 | 390 | |
| b | 2023 | Qtr4 | 33 | 456 | |
| b | 2023 Total | 456 | |||
| b Total | 300 | 456 | |||
| Grand Total | 456 |
Desired Output
| Type | Date (Year) | Date (Quarter) | Sum of Value | ToDate |
| a | 2022 | Qtr1 | 6 | 6 |
| a | 2022 | Qtr2 | 15 | 21 |
| a | 2022 | Qtr3 | 24 | 45 |
| a | 2022 | Qtr4 | 33 | 78 |
| a | 2022 Total | 78 | 78 | |
| a | 2023 | Qtr1 | 6 | 84 |
| a | 2023 | Qtr2 | 15 | 99 |
| a | 2023 | Qtr3 | 24 | 123 |
| a | 2023 | Qtr4 | 33 | 156 |
| a | 2023 Total | 78 | 156 | |
| a Total | 156 | 156 | ||
| b | 2022 | Qtr1 | 42 | 42 |
| b | 2022 | Qtr2 | 51 | 93 |
| b | 2022 | Qtr3 | 60 | 153 |
| b | 2022 | Qtr4 | 69 | 222 |
| b | 2022 Total | 222 | 222 | |
| b | 2023 | Qtr1 | 6 | 228 |
| b | 2023 | Qtr2 | 15 | 243 |
| b | 2023 | Qtr3 | 24 | 267 |
| b | 2023 | Qtr4 | 33 | 300 |
| b | 2023 Total | 78 | 300 | |
| b Total | 300 | 456 | ||
| Grand Total | 456 | 456 |
Thank you all for the guidance,
I found the desired result through the use of "AllExcept"
ToDate=CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Type] ),
Table1[Date] <= MAX ( Table1[Date] )
)
)Just preliminary, but i think it does what i'm looking for...
Love the forum.. thanks for giving the guidance that led me to a solution that works.
4 Replies
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistOnce you have a date dimension, use that in your measure to get the running total and also in the visualization.
Try using the following measure and see if that works.
Running Total by Category Group =VAR CategoryGroupFilter = SELECTEDVALUE(Table1[Type])VAR RunningTotal =IF(CategoryGroupFilter = "a",SUMX(FILTER(ALL(Table1),Table1[Type] = "a" &&Table1[Date] <= MAX(Table1[Date])),Table1[Value]),IF(CategoryGroupFilter = "b",SUMX(FILTER(ALL(Table1),Table1[Type] = "b" &&Table1[Date] <= MAX(Table1[Date])),Table1[Value]),BLANK()))RETURNRunningTotalThank you all for the guidance,
I found the desired result through the use of "AllExcept"
ToDate=CALCULATE (
SUM ( Table1[Value] ),
FILTER (
ALLEXCEPT ( Table1, Table1[Type] ),
Table1[Date] <= MAX ( Table1[Date] )
)
)Just preliminary, but i think it does what i'm looking for...
Love the forum.. thanks for giving the guidance that led me to a solution that works.
