Matrix - Reset Cumulation

[CEllis]

I've been restling with this for a few days now and cant seem to get it right.

I am using a dax statment to provide a cummulation figure in a matrix that works.

CALCULATE(SUM('Attendance (Table)'[Present]),
    FILTER(
            ALL('Academic Year (Table)'),
             'Academic Year (Table)'[Week Ending]<= MAX('Academic Year (Table)'[Week Ending])))

The next step was to  - Check to see
if the Total Marks = Present then continue 

CALCULATE(SUM('Attendance (Table)'[Present]),
FILTER(
ALL('Academic Year (Table)'),
'Academic Year (Table)'[Week Ending]<= MAX('Academic Year (Table)'[Week Ending])))

Then to check if they had reached the threshold 

if the Cumulation reached 21 then reset the Cumulation to 0

Any help greatly appreciated.

[AlexisOlson · Accepted solution]

I don't fully understand what you're trying to do but you could try using MOD.

VAR _Present =
    CALCULATE (
        SUM ( 'Attendance (Table)'[Present] ),
        FILTER (
            ALL ( 'Academic Year (Table)' ),
            'Academic Year (Table)'[Week Ending]
                <= MAX ( 'Academic Year (Table)'[Week Ending] )
        )
    )
RETURN
    MOD ( _Present, 21 )

[CEllis]

@AlexisOlson Thanks for the respone.

The outcome I am looking for is to flag at the point it hits 21 and then continue the count from 0 to see whn the next 21 occures.

I do appreciate your help with this.

Week Ending			Fri 06 Sep			Fri 13 Sep			Fri 20 Sep			Fri 27 Sep			Fri 04 Oct			Fri 11 Oct			Fri 18 Oct
Anon	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative	Total Marks	Present	Cumulative
Pupil 8972	8	6	0	10	10	10	10	4	0	8	8	8	10	8	0	10	10	10	10	8	0
Pupil 9078	8	6	0	10	10	10	10	10	20	8	0	0	10	0	0	10	10	10	10	10	20
			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or			if Total Marks = Present Sum(Present) else 0 or
			if Total Marks =21 then 21			if Total Marks =21 then 21			if Total Marks =21 then 21			if Total Marks =21 then 21			if Total Marks =21 then 21			if Total Marks =21 then 21			if Total Marks =21 then 21