Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Share sample pbix file to help you.
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
@lbendlin @Greg_Deckler @Ahmedx @Sahir_Maharaj @Arul @Ashish_Mathur @Padycosmos @bolfri - Much appriciated you help here. Thanks!