DAX Formula to group / summarize until sum in other column is met

[Rionick]

Hello!

Imagine this situation:

Pick Up Week	Volume
2023-39	3.45
2023-45	16.47
2023-46	16.01
2023-47	2.85
2023-48	16.76
2023-49	22.71
2023-51	0.67
2023-52	11.29
2023-53	22.36
2024-1	8.16
2024-2	10.06

Would need a calculated column which can return the first weeknum of the group based on the running total of the volume column. Each group can have the max sum of 63. If by adding the next row we go over 63 a new group should start. The result would be like this:

Pick Up WK	Volume	Running Total	Grouped Pick Up WK
2023-39	3.45	3.45	2023-39
2023-45	16.47	19.92	2023-39
2023-46	16.01	35.93	2023-39
2023-47	2.85	38.78	2023-39
2023-48	16.76	55.54	2023-39
2023-49	22.71	22.71	2023-49
2023-51	0.67	23.38	2023-49
2023-52	11.29	34.67	2023-49
2023-53	22.36	57.04	2023-49

Running total is just to show where the count should stop, I would need the Grouped Pick Up WK column only.

Any help aproaching this problem would be most welcome!

[v-shex-msft · Accepted solution]

HI @Rionick,

I add two more calculate columns in the table to help remark the start pickup week, you can take a look the following calculate column formulas:

WeekNumber =
VALUE (
    SUBSTITUTE ( [Pick Up Week], "-", IF ( LEN ( [Pick Up Week] ) < 7, "0", "" ) )
)

ModRolling =
VAR _offset = 60
VAR rolling =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[WeekNumber] <= EARLIER ( 'Table'[WeekNumber] ) )
    )
RETURN
    MOD ( rolling, _offset )

Grouped Pick Up WK = 
VAR _end =
    MAXX (
        FILTER (
            'Table',
            [WeekNumber] <= EARLIER ( 'Table'[WeekNumber] )
                && [ModRolling] > EARLIER ( 'Table'[ModRolling] )
        ),
        [WeekNumber]
    )
RETURN
    MINX ( FILTER ( 'Table', [WeekNumber] > _end ), [Pick Up Week] )

Regards,

Xiaoxin Sheng