Cumulative sum is not working for current year for columns from different tables

[akhilduvvuru]

 Hi Team, I'm facing issues with Cumulative sum from different columns.

Year is from Calender Table and Col B is from Table 1. Both Calender table and Table1 has join. Col C is from Table 2. Table 1 and Table 2 has join.

Both Table 1 and Table 2 are direct query.

Calender[Year]	Table 1[Col B]	Table 2 [Col C]	Table 1 [Flag]	Measure1	Cumulative
2008	246		N	246	246
2009	317		N	317	563
2010	263		N	263	826
2011	254		N	254	1080
2012	823		N	823	1903
2013	149		N	149	2052
2014	430		N	430	2482
2015	796		N	796	3278
2016	344		N	344	3622
2017	715		N	715	4337
2018	938		N	938	5275
2019	418		N	418	5693
2020	620		N	620	6313
2021	89		N	89	6402
2022	288		N	288	6690
2023	951	555	Y	555	7641(7245)
2024				0	7641
2025				0	7641
2026				0	7641
2027				0	7641
Total				7245

Now my requirement is, I have created a measure1 as below which is working as expected.

Measure1 = If( MAX(Table1 [Flag])= "Y", Table2 [Col C], Table 1 [Col B])

Now I want to calculate cumulative sum on Measure1 which is not working for 2023. (For some reason it is still considering the value from Table1 [ColB] only. But Ideally it should consider the values in Measure 1). Till 2022 all works as expected. However for 2023 it should be 6690+555 = 7245. But it is adding 6690+951 from ColB = 7641
Here is my Cumulative measure. My logic is right but I thing I'm missing someting

Cumulative=
CALCULATE(
[Measure1],
Filter(
ALL('CALENDER'),
'CALENDER'[Year) <= MAX('CALENDER'[Year))
)
)

I have a year filter.

Can someone please help me with the same.

Thanks!

[Ahmedx]

Share sample pbix file to help you.

[Ahmedx]

I'm not sure, but try writing like this

Cumulative=

VAR _t1 =[Measure1]
RETURN
CALCULATE( _t1
,
Filter(
ALL('CALENDER'),
'CALENDER'[Year) <= MAX('CALENDER'[Year))
)
)
----
OR
Cumulative=

VAR _t1 =ADDCOLUMNS(VALUES(Calender[Year]),"SUM",[Measure1])
RETURN
SUMX (_t1,[SUM])

[akhilduvvuru]

@Ahmedx - Thanks for your response. Both the measures are returning same Measure1 values.

One observation is when I use your first logic, values are showing as Measure1 but total is showing the same 7641. However, if I use the second logic the values are same as Measure1 but the total is showing 7245

[akhilduvvuru]

@lbendlin @Greg_Deckler @Ahmedx @Sahir_Maharaj @Arul @Ashish_Mathur @Padycosmos @bolfri - Much appriciated you help here. Thanks!