Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Cumulative Sum

Good afternoon.

My problem is this. I need to create a measure that makes an accumulated sum based on a month (Category) That is, if I select January I add only January, but if I select the period Jan-Jun I add from January to June, I want to clarify that I do not have a calendar table and it is not functional for me to create one due to the nature of my board.

I have some Month labels, and a 1-12 code that orders the months.

I have a table where the sales are in one column and the months in another column and the code in another.

I tried some solutions that I found researching and if it does make me the sum however when I filter the period it stops doing the cumulative sum and only shows me the sum of that month.

Here we see how it shows me the oslolo of May, and not the sum of January to May.

The Measurement I made is as follows.

Dynamic Cumulative =

VAR _MY=
MAX(Months[Code])

VAR _MESSELECCIOANDO=
SELECTEDVALUE(Months[Code])

VAR _ACCUMULATED=
CALCULATE([Exercise], FILTER(ALLSELECTED(Months),Months[Code]<= _MY))
RETURN

_ACCUMULATED
The code is the numerical order of the months
I would like you to help me see how I do so that when I select the period, I add up
I appreciate your help.
Best regards.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Syndicate_Admin, 

     

    Would you like to calculate the cumulative value from January to the month selected in the filter? If so, let me explain why you might be facing this issue.

    The point is that the ALLSELECTED function retains the filter context from the user interface (such as reports or slicers), so it cannot completely ignore the effects of filters.Please use ‘ALL’ instead, I’ve made a test for your reference,

     

    Dynamic Cumulative =

VAR _MY=
MAX(Months[Code])

VAR _MESSELECCIOANDO=
SELECTEDVALUE(Months[Code])

VAR _ACCUMULATED=
CALCULATE(Sum(Months[Exercise]), FILTER(ALL(Months),Months[Code]<= _MY))

RETURN
_ACCUMULATED

     

     

     

     

     

     

    Best Regards,

    Bof

     

     

     

     

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Syndicate_Admin, 

     

    Would you like to calculate the cumulative value from January to the month selected in the filter? If so, let me explain why you might be facing this issue.

    The point is that the ALLSELECTED function retains the filter context from the user interface (such as reports or slicers), so it cannot completely ignore the effects of filters.Please use ‘ALL’ instead, I’ve made a test for your reference,

     

    Dynamic Cumulative =

VAR _MY=
MAX(Months[Code])

VAR _MESSELECCIOANDO=
SELECTEDVALUE(Months[Code])

VAR _ACCUMULATED=
CALCULATE(Sum(Months[Exercise]), FILTER(ALL(Months),Months[Code]<= _MY))

RETURN
_ACCUMULATED

     

     

     

     

     

     

    Best Regards,

    Bof

     

     

     

     

     

     

  • ahadkarimi's avatar
    ahadkarimi
    Solution Specialist
    2 years ago

    Hey Syndicate_Admin, try this code and let me know if there is any problem.

    Dynamic Cumulative =
VAR _MAX_MONTH =
    MAX(Months[Code])
VAR _ACCUMULATED =
    CALCULATE(
        [Exercise],
        FILTER(
            ALLSELECTED(Months),
            Months[Code] <= _MAX_MONTH
        )
    )
RETURN
_ACCUMULATED