Cumulative Sum with two filters

[um_mir]

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

This [NetGrowthCumulative] measure works for one year window only. It resets for every year. Please see my screen shot below where from 352 to 361 is year 2018 & 362 to 363 is year 2019.

How can i fix this? Any solutions will be much appreciated.

Thank you

[v-frfei-msft]

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

[um_mir]

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

[um_mir · Accepted solution]

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