Forum Discussion
Cumulative Sum with two filters
Hi Experts,
Can someone please help me with below issue. I have data like this:
TableA
| SequenceNumber | Year |
| 130 | 2016 |
| 131 | 2016 |
| 132 | 2017 |
| 133 | 2017 |
| 134 | 2018 |
| 135 | 2018 |
| 136 | 2019 |
Measure [NetGrowth] works good.
My [NetGrowth] cumulative measure is
NetGrowthCumulative =
VAR MyYearMin = MINX(TableA,TableA[Year])
VAR MyYearMax = MAXX(TableA,TableA[Year])
RETURN
SUMX(
FILTER(
ALL(TableA),TableA[SequenceNumber]<=MAX(TableA[SequenceNumber])
&& TableA[Year]>=MyYearMin && TableA[Year]<=MyYearMax
),
[NetGrowth]
)
Hello,
I got this working. Hope below solution will help someone:
1. Created a measure in TableA to get selected Year
SelectedYear = VAR t = ALLSELECTED(TableA[Year]) RETURN IF(MAX(TableA[Year]) in t,1,0)
2. Cumulative Measure code
NetGrowthCumulative = SUMX( FILTER( ALLEXCEPT(TableA,TableA[Year]), TableA[SeqNo]<=MAX(TableA[SeqNo]) && [SelectedYear]=1 ), [NetGrowth])3. Make sure there is relationship between TableA & TableB.
Regards
3 Replies
v-frfei-msftCommunity Support
Hi um_mir,
What is your meausre [NetGrowth]? Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.
Regards,
Frank
Hi v-frfei-msft
Thank you for your quick response. Please see the link for the pbxi file. I have reproduced the issue. Cummulative sum works for a year and it resets again in the next year. For the attached example NetGrowthCumulative I'm exepecting below result:
Year SeqNo NetGrowth NetGrowthCumulative 2018 1 100 100 2018 2 50 150 2018 3 95 245 2018 4 -50 195 2018 5 120 315 2019 6 20 335 2019 7 80 415 Regards
Hello,
I got this working. Hope below solution will help someone:
1. Created a measure in TableA to get selected Year
SelectedYear = VAR t = ALLSELECTED(TableA[Year]) RETURN IF(MAX(TableA[Year]) in t,1,0)
2. Cumulative Measure code
NetGrowthCumulative = SUMX( FILTER( ALLEXCEPT(TableA,TableA[Year]), TableA[SeqNo]<=MAX(TableA[SeqNo]) && [SelectedYear]=1 ), [NetGrowth])3. Make sure there is relationship between TableA & TableB.
Regards