Running totals with value only suit the criteria

[Anonymous]

Hi , I try to calculate 8 weeks of running total thats only suits the criteria.

Like only have the running totals that have whole 8 weeks. see picture below:

Weeknr	Count	Rolling 8 weeks count	week
202302	10	0
202303	20	0
202304	30	0
202305	50	0
202306	30	0
202307	20	0
202308	40	0
202309	65	265	202302-202309
202310	35	290	202303-202310
202311	5	275	202304-202311
202312	15	260	202305-202312
202313	10	220	202306-202313

Thanks for help.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to create a measure with below dax formula:

Rolling 8 weeks count =
VAR cur_wk =
    SELECTEDVALUE ( 'Table'[Weeknr] )
VAR min_wk =
    MINX ( ALL ( 'Table' ), [Weeknr] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Weeknr] <= cur_wk && [Weeknr] >= cur_wk - 7 )
RETURN
    IF ( cur_wk - min_wk >= 7, SUMX ( tmp, [Count] ), BLANK () )

Please refer the attaced .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.