topic Re: Calculating the SUM of a subset from a running total in DAX Commands and Tips

Calculating the SUM of a subset from a running total

UBComma — Fri, 06 May 2022 13:50:50 GMT

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:

Re: Calculating the SUM of a subset from a running total

tamerj1 — Fri, 06 May 2022 15:19:52 GMT

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]?

Re: Calculating the SUM of a subset from a running total

AlexisOlson — Fri, 06 May 2022 15:28:28 GMT

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.

Re: Calculating the SUM of a subset from a running total

UBComma — Fri, 06 May 2022 16:19:35 GMT

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

Re: Calculating the SUM of a subset from a running total

UBComma — Fri, 06 May 2022 16:20:26 GMT

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

Re: Calculating the SUM of a subset from a running total

tamerj1 — Sat, 07 May 2022 13:47:36 GMT

UBComma

Re: Calculating the SUM of a subset from a running total

UBComma — Sat, 07 May 2022 14:06:07 GMT

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