Dynamic Cumulative Sum

[uscmea]

Hi,

I have constructed a cumulative measure (called OBJ Cumulative) with the following formula:

OBJ Cumulative =

CALCULATE (

SUM (OBJ[Matrículas] ),

FILTER (

ALLSELECTED ( Mes_Internacional ),

ISONORAFTER(Mes_Internacional[Num Mes Internacional_Conv]

,MAX ( Mes_Internacional[Num Mes Internacional_Conv] ),DESC)))

Where Num Mes Internacional_Conv is an index column that goes from 0 to 12.

When I have done this, I get the right cumulative results, as can be seen below:

However, when I try to filter with Num Mes Internacional_Conv the OBJ Cumulative measure, I obtain Matrículas instead of OBJ Cumulative measure:

That is, instead of getting 190, I observe 37 (I do not see my measure cumulated until Num Mes Internacional_Conv 3)

Could you help me out, please?

Thank you very much in advance!

Best,
Sara

[v-alq-msft · Accepted solution]

Hi, @uscmea 

You may try to modify 'ALLSELECTED' with 'All'. Here is my test. The column 'Matriculas' is from OBJ Table. I put it in the same table for test.

OBJ Cumulative = 
CALCULATE (
    SUM (Mes_Internacional[Matriculas]),
    FILTER (
        ALL(Mes_Internacional ),
        ISONORAFTER (
                Mes_Internacional[Num Mes Internacional_Conv], MAX ( Mes_Internacional[Num Mes Internacional_Conv] ), DESC
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[uscmea]

Thank you very much for your help! By changing ALLSELECTED for ALL, my formula works perfectly fine 🙂

[JarroVGIT]

Hi @uscmea ,

Your context is changing as soon as you apply a filter. Let me explain in your formula. 

OBJ Cumulative =
CALCULATE (
SUM (OBJ[Matrículas] ),
FILTER (
ALLSELECTED ( Mes_Internacional ), --> all items (which is now all rows where period = sliced to period)
ISONORAFTER(Mes_Internacional[Num Mes Internacional_Conv]
,MAX ( Mes_Internacional[Num Mes Internacional_Conv] ),DESC))) --> there are no periods before or after the current period in the context this is evaluated.

I think you will need to use REMOVEFILTERS() somewhere but I have little experience in that.

If possible I would love to figure this one out (can't do it though from the top of my head). Is it possible to share your PBIX (with no confidential data)? If  you don't want to share publicly you can share via PM with me and I'll solve this one for you 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[uscmea]

Thank you very much for your help!

By changing ALLSELECTED for ALL, the formula works fine! Just FYI 🙂

[JarroVGIT]

You might want to try another aproach all together:

OBJ Cumulative =
VAR _curCONV = SELECTEDVALUE(Mes_Internacional[Num Mes Internacional_Conv])
RETURN
CALCULATE (
SUM (OBJ[Matrículas] ),
FILTER (
ALLEXCEPT ( Mes_Internacional , <columns that must be respected>),
Mes_Internacional[Num Mes Internacional_Conv] <= _curCONV))

Should work in both visuals 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂