Forum Discussion

Chamara's avatar
Chamara
Advocate I
9 years ago
Solved

Cumulative sum with VALUES filter issue?

I want to show the Cumulative sum of positive numbers. I also want to have another Cumulative sum that adjusts with some slicers. I created 2 measures to do this. Cumulative Sum works fine. Hower my ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Chamara,

    Please use the following formula to create Cumulative Sum Filtered measure and check if you get expected result.

    Cumulative Sum Filtered = CALCULATE (
        SUM ( Data1[Revenue] ),
           FILTER(ALLEXCEPT(Data1,Data1[Classification]), Data1[TargetDate] <= MAX ( Data1[TargetDate])
    )
    )




    Thanks,
    Lydia Zhang

Chamara's avatar
Chamara
Advocate I
9 years ago

Anonymous, Thank you for the reply. My main issue is when no value is selected on the slicer, I expect the "Cumulative sum Filtered" to match with "Cumulative Sum" for all years (the two colored columns show this). Following are my expected values.

For example, 2022 "Cumulative sum" is derived as by summing up "Sum of Revenue" for all years up until 2022 inclusive = $199,568,880 +  $90,584,908 +  $20,245,022 +  $1,228,799 + $320,305,949 = $511,196,490. This value for 2022 matches for both "Cumulative Sum" and "Cumulative Sum Filtered" somehow in the Power BI report I attached.  Also "Cumulative Sum" calculation has expected values for each year which is the "Sum of Revenue" for current year + "Cumulative Sum" for previous year in the same report. Question is, why don't I see the same values as the above table for "Cumulative Sum Filtered" in Power BI report when nothing is selected on the slicer.

 

The general formula for

"cumulative sum for current year" = "cumulative sum for previous year"  + "sum for current year"

With this, if sum for current year is 0, then "cumulative sum for current year" should match "cumulative sum for previous year" (i.e. "cumulative sum for current year" cannot go below what was the "cumulative sum for previous year" ). The only way cumulative sum from one year to another can go down is if the "sum for current year" is negative.

 

With that, I do not get your logic to getting a smaller value for 2020. Even if that logic is true, then it does not explain how the "Cumulative Sum Filtered" catches up with "Cumulative Sum" in 2022 after dipping in 2020 and 2021.

 

Thank you,

Chamara

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Chamara,

Please use the following formula to create Cumulative Sum Filtered measure and check if you get expected result.

Cumulative Sum Filtered = CALCULATE (
    SUM ( Data1[Revenue] ),
       FILTER(ALLEXCEPT(Data1,Data1[Classification]), Data1[TargetDate] <= MAX ( Data1[TargetDate])
)
)




Thanks,
Lydia Zhang

  • Chamara's avatar
    Chamara
    Advocate I
    9 years ago

    AnonymousYour DAX actually give the data I need and I marked it as the answer. Thank you for that. However, I believe the formula I had initially for 'Cumulative Sum Filtered' should give the same results and that it is a bug. I do not discount the third possibility that I have not understood the DAX used in that. Either way, I would like to get closure on that as well and will log a bug in couple of days with that intention.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hey, I am new to power BI and I have some query related to the same topic. Can I put cumulative sum based on dates in a number card and then filter it for specific dates using a date slicer. I am currently using Datesmtd dax as i only want to calculate cumulative sum month by month.