Group Running total for date and type

[ArbWare]

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

[ArbWare · Accepted solution]

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.

[parry2k]

 @newtodaxI wouldn't go that path, there are 2 ways to solve a problem, improve the model and write a simple DAX, or write a complex DAX and then deal with performance and other challenges. Just my 2 cents.  

[newtodax]

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()

        )

    )

RETURN

    RunningTotal

[parry2k]

@ArbWare 

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 Playlist

Once you have a date dimension, use that in your measure to get the running total and also in the visualization.