Problem with cumulation total measure with missing events

[GUJEN]

Hi

I'm quite new to Power BI and I have run into problems regarding Measures of running / cumulative totals. I have checked this forum, tried a lot of suggestions but without any luck. The problem is as follow.

My data can for this be reduced to the following:

1) ID; Country; Grade_V2; StartDate;StartYear; EventDate;MonthEventDate (days between startdate and eventdate in month)

2) Index: A table with integers 1 to 60

Then I have the following measure:

CummulativeEvents = CALCULATE(sum(BLB[MonthEventtBin]),filter(all(BLB[MonthEventtBin]),BLB[MonthEventtBin]<=max(BLB[MonthEventtBin])))

That doesn't work. For some reason, as seen from the two pictures below, CummlativeEvents returns the product between the INDEX and the Count of MonthEventBin and calculated the running total. Why? What is the mistake I'm making?

Picture 1Total

What I would like to see is the last value(s) in Cumulative event should be equal to the total. In the first case 24, then 31.

A couple of important information’s more:

There is not an event for every row - less than 10% of the rows have an event.

The data table 1 is not sort.

There is a relationship between Index and MonthEventBin.

[v-ljerr-msft · Accepted solution]

@GUJEN

In order to calculate the cumulative totals for "MonthEventtBin" by "Index" in your scenario, you need to apply a filter to the "Index" column instead of the "MonthEventtBin" column. The formula below is for your reference.

CummulativeEvents =
CALCULATE (
    SUM ( BLB[MonthEventtBin] ),
    FILTER ( ALL ( BLB[Index] ), BLB[Index] <= MAX ( BLB[Index] ) )
)

Regards