Cumulative sum measure with multiple selected filters

[Anonymous]

Hi, I've been following this guide to create a cumulative sum measure and it works perfectly fine, expect I want to be able to use it for all time resolutions I have in my custom hierachy.

I want to be able to see the cumulative sum for each month, week and day while I at the same time use a slicer to define the time-period I want to view the data.

My current measure looks like this:

acc_buy = 
sumx(
    FILTER(
        ALLSELECTED(TimeQuery),
        TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) 
    
    ),


    [BUY_DIFF] 
)

Below is the code i assumed would work:

acc_buy = 
sumx(
    FILTER(
        ALLSELECTED(TimeQuery),
        TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) ||
        TimeQuery[WeekNumber] <=MAX(TimeQuery[WeekNumber]) ||
        TimeQuery[Days] <=MAX(TimeQuery[Days]) 
    
    ),


    [BUY_DIFF] 
)

[Anonymous]

Hi @Anonymous ,

Can you show some sample data and expected result to us?

Best regards,

Jay

[amitchandak]

@Anonymous , the best way is to use it with date.

acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[Date] <=MAX(TimeQuery[Date])

),

[BUY_DIFF]
)

The last one should work with &&.

acc_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) &&
TimeQuery[WeekNumber] <=MAX(TimeQuery[WeekNumber]) &&
TimeQuery[Days] <=MAX(TimeQuery[Days])

),

[BUY_DIFF]
)

If you change <= to = at year level, it will reset total at year level

[Anonymous]

Before I go into detail how your code worked with my data, I might have to add that there are two measures both being cumulative summed and then added togheter.

So, I tried your code and I noticed it doesn't really work for a longer period of time, say 3 or 4 months and becomes discontinuous, see image below:

The upper graph is your code and the graph below is my code.

The original problem still remains though, that it doesn't aggregate the correct way even with your code.