Multiple Measures to give me a running rate

[Ian_20210927]

Hi. I have created 3 matrix tables (below). The first sums the KS (this stands for school key stages) per day. The second table is the running 7 days sum of those daily totals. And the third table is that 7 day total/divided by denominator *100000

For some reason the third table doesn't do the maths unless something is added or subtracted from the 7 day running sum. If the 7 day total stays the same on the next day (and is a zero in the daily table), it erroneously defaults to zero in the third table when there will/may be daily totals for some of the other days in the 7 days period. I don't understand why it's doing this, because it works fine other than that last bit. Thanks for taking the time, Ian

The measure I'm using to get the daily total:

M3 =
VAR result =
    CALCULATE (
        COUNT ( Project4_data[Key Stage]),
        FILTER (
            ALLSELECTED ( Project4_data ),
            [Specimen Date] IN VALUES ( Project4_data[Specimen Date] )
                && [Key Stage] IN VALUES ( 'BreakthroughTable'[Key Stage] )
        )
    )
RETURN
    IF ( result <> BLANK (), result, 0 )

7 day running sum measure:

7 Day Sum of Cases =
Var WeekCount =
CALCULATE([M3],
FILTER( ALL( Project4_data[Specimen Date]),
Project4_data[Specimen Date] > MAX( Project4_data[Specimen Date]) - 12 &&
Project4_data[Specimen Date] <= MAX(Project4_data[Specimen Date]) -5 ))
RETURN
IF(
WeekCount = BLANK(),
0,
WeekCount)

And the measure to work out the rate per 100,000:

Key Stages 7D Rate =
VAR KS7DRate =
DIVIDE([7 Day Sum of Cases], [KS Denominator])*100000
RETURN
IF(
KS7DRate = BLANK(),
0,
KS7DRate
)

 

[Ian_20210927]

I should add that I've been pulling my hair out for days and am now totally bald. Thanks for looking, Ian

 

[Ian_20210927]

Hi again, I've worked out that it must be the lookup I use to pick which denominator to use. The denominators are in a table:

Denominators Denominators are picked using a measure which filters the first column to the correct KS -

KS Denominator =
Var KSDnom =
CALCULATE(
SUM(KS_Denom[KS Denominators]),
        FILTER (
            ( KS_Denom ),
            [Key Stage] IN VALUES ( 'BreakthroughTable'[Key Stage] )
        )
    )
RETURN
    IF ( KSDnom <> BLANK (), KSDnom, 0 )

So I'm getting closer - but still not over the line