Forum Discussion
Problem with filter context, or context transition (I think)
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:
Hi UBComma ,
Due to I don't know your data model, I create a sample to have a test. I create a Year table by generateseries function.
[RT] in my sample:
RT = VAR _START = 1 VAR _BEGIN_YEAR = 2022 RETURN (MAX(Years[Year]) - _BEGIN_YEAR) * _STARTResult is as below.
In my side, [Sum of Annual Values for past 15 years] measure works well. I think your problem my be casued by [RT] , measure. You can try to add ALL, ALLSELECTED or ALLEXCEPT function in your measure. Or you can share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi UBComma ,
Due to I don't know your data model, I create a sample to have a test. I create a Year table by generateseries function.
[RT] in my sample:
RT = VAR _START = 1 VAR _BEGIN_YEAR = 2022 RETURN (MAX(Years[Year]) - _BEGIN_YEAR) * _STARTResult is as below.
In my side, [Sum of Annual Values for past 15 years] measure works well. I think your problem my be casued by [RT] , measure. You can try to add ALL, ALLSELECTED or ALLEXCEPT function in your measure. Or you can share a sample file with me and show me a screenshot with the result you want. This will make it easier for me to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
