cancel
Showing results for
Did you mean:
Helper 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] 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.

Thanks!

4 REPLIES 4
Solution Sage

Solution Sage

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=

RETURN
SUMX (_t1,[SUM])``````

Helper II

@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

Helper II

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors