Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
akhilduvvuru
Advocate II
Advocate II

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

 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]Measure1Cumulative
2008246 N246246
2009317 N317563
2010263 N263826
2011254 N2541080
2012823 N8231903
2013149 N1492052
2014430 N4302482
2015796 N7963278
2016344 N3443622
2017715 N7154337
2018938 N9385275
2019418 N4185693
2020620 N6206313
202189 N896402
2022288 N2886690
2023951555Y5557641(7245)
2024   07641
2025   07641
2026   07641
2027   07641
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!

4 REPLIES 4
Ahmedx
Super User
Super User

Share sample pbix file to help you.

Ahmedx
Super User
Super User

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

 

 

@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
Advocate II
Advocate II

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.