Calculating the SUM of a subset from a running total

[UBComma]

@AlexisOlson Any thoughts on this one?

I am trying to write a measure that can be updated by parameter values. I had this working as a calculated table but I'm having trouble getting one of the measures to work. Essentially what I am trying to do is calculate the sum of a specific set of values in a running total. The set is defined by years (I need to go from the present year back 15 years in this case). Once I have that SUM I'm entering it into the PMT function.

This is the measure I'm trying to fix........

Sum of Annual Values for past 15 years =

VAR Rate = .0275

VAR Periods = 15

VAR PeriodSum =

    SUMX (

        CALCULATETABLE (

            ADDCOLUMNS (

                Years,

                "Running Total", [RT]

            ),

            Years[Year] <= SELECTEDVALUE ( Years[Year] ) &&

            Years[Year] > SELECTEDVALUE ( Years[Year] ) - Periods

        ),

        [Running Total]

    )

RETURN

    PMT ( Rate, Periods, - PeriodSum )

The above measure is placed in a visual, say a table, the first column is Years[Year], and then the measure would follow.

My Years table is this: Years = GENERATESERIES (2022 , 2022 + 50, 1 )

And the [RT] measure is a value that increments up each year.

When I run the measure I get the correct values for the first year, 2022, through 2037, and then (after 15years) it goes flat and returns the same value for the rest of the years. I'm guessing there's something wrong with my filter context of Years[Year] but I can't figure it out. If anyone can offer a suggestion, I will greatly appreciate it.

Graphically, this is what I'm trying t do:

[UBComma]

Absolutely GENIUS 😊. thank you so much for helping me with this one. It really had me stumped.

[AlexisOlson]

The SELECTEVALUE inside the CALCULATETABLE seems strange to me.

I'd try refactoring a bit:

Sum of Annual Values for past 15 years =
VAR Rate = .0275
VAR Periods = 15
VAR CurrYear = SELECTEDVALUE ( Years[Year] )
VAR YearRange =
    FILTER (
        ALL ( Years ),
        Years[Year] <= CurrYear &&
        Years[Year] > CurrYear - Periods
    )
VAR PeriodSum =
    SUMX (
        ADDCOLUMNS ( YearRange, "Running Total", [RT] ),
        [Running Total]
    )
RETURN
    PMT ( Rate, Periods, - PeriodSum )

I'm not sure this will do exactly what you want, but IMO, it's easier to debug if it doesn't.

[UBComma]

Thank you, I'll rewrite it in the fashion you suggested and try some additional debugging.

[tamerj1]

Hi @UBComma 

can you share a screenshot of your table visual including both measures [RT] and [Sum of Annual Values for past 15 years]. What is the code for [RT]?

[UBComma]

The code above was a sample. I sent the actual code in a private message to you

[tamerj1 · Accepted solution]

@UBComma