Forecasting is not summing up

[pfuschi]

Hi all
I need your help in a DAX measure, which doesn't sum up...... I already tried with calculate and sumx, but haven't found a solution.

I have two tables.
- table "pupils" with one entry per year (2010 - 2021 ) and age  of students (0-16)

And a second table "years" with entries from 2010 - 2040.

So far I have this matrix:

my goal is to have a what-if analysis to see, what would be the distribution in the future, if the parameter "births" changes.
I already achieved this, as you can see in this picture.

but unfortunately, the measure doesn't sum up, starting in 2022 onwards!!

Here is my measure:

test = 
VAR PupilsCount = sum('Pupils'[count])
VAR Diff = MAX(Year[Year]) - 2021
VAR AgeInt = max('Pupils'[Age]) 
VAR BirthNr = SELECTEDVALUE(Births[Births]; 13)
VAR PupFC = LOOKUPVALUE('Pupils'[count]; 'Pupils'[count];max('Pupils'[count])-diff ; 'Pupils'[Year]; max(Year[Year]) - diff) 
VAR Pup = IF (PupilsCount <> BLANK() ;    
    PupilsCount ; 
    if (AgeInt=0; 
        BirthNr; 
        if (PupFC = 0 ; Births; PupFC 
    )))
RETURN
Pup

What I need is a measure, which sums up from 2010 until 2040 and not only until 2021.

Thx for your help.

[v-shex-msft · Accepted solution]

Hi @pfuschi,

If you mean look up suitable records based on year and age, you can refer to below steps.

1. Create a table stored year and not has relationship to original table.

Year Range = GENERATESERIES(2010,2040,1) 

2. Create matrix visual with age as row(original table), year as column(year range table), lookupvalue measure as value. 

Look up =
VAR currAge =
    LASTNONBLANK ( 'Sample'[Age], [Age] )
VAR currYear =
    SELECTEDVALUE ( 'Year Range'[Year] )
VAR lookup =
    LOOKUPVALUE (
        'Sample'[Count],
        'Sample'[Age], currAge,
        'Sample'[Year], currYear
    )
RETURN
    IF (
        lookup <> BLANK (),
        lookup
    )

3. Modify measure to add bin variable to store diff between year and age, use this variable to lookup value from previous records.

Look up 2 = 
VAR currAge =
    LASTNONBLANK ( 'Sample'[Age], [Age] )
VAR currYear =
    SELECTEDVALUE ( 'Year Range'[Year] )
VAR lookup =
    LOOKUPVALUE (
        'Sample'[Count],
        'Sample'[Age], currAge,
        'Sample'[Year], currYear
    )
VAR bin = currYear - currAge
RETURN
    IF (
        lookup <> BLANK (),
        lookup,
        CALCULATE (
            MAX ( 'Sample'[Count] ),
            FILTER ( ALL ( 'Sample' ), [Year] - [Age] = bin )
        )
            + 0
    )

BTW, if you want to summarize all suitable records, please use sum function to replace max function which I used in if statement.

Regards,

Xiaoxin Sheng