cancel
Showing results for
Did you mean:
Regular Visitor

## Cumulative sum of a measure

Hi

I have my facts table (fSales) and dates table (dDates) and a measure calculating the cumulative number of sales. My meassures looks like this

CALCULATE(
FILTER
(ALLSELECTED(fSales);
fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])
)
)

When I create a table with the running total across years I'm getting the correct results. (table on the left below)

However, when I create a matrix and I ask for the running total by service plan (row) and year (column) then I'm getting incomprehensible results.

why does this happen? it is as if the implicit filter which is the service plan does not apply to the measure.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Cumulative sum of a measure

Hi @chronis69,

Please check the following steps as below.

1. Create a date table and a calculated column in the date table.

`date = CALENDARAUTO()`

`YEAR = YEAR('date'[Date])`

2. Create a calcualted table.

`y = VALUES('date'[YEAR])`

3. Create the relationships between tables.

4. Create the measure to get the cumulative running total.

```Licenses_Running Total matrix =
VAR maxyear =
MAX ( 'y'[YEAR] )
VAR total =
CALCULATE (
FILTER ( ALL ( 'date' ), 'date'[YEAR] <= maxyear )
)
RETURN
IF ( ISBLANK ( total ), 0, total )

```

Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Cumulative sum of a measure

Frank thanks for the solution. Fiddling about I found another way that solved the problem. I replaced ALL with ALLEXCEPT.

this prevented the measure calculation from removing the implicit filter applied by [service plan 2] and hey !!! it worked.

CALCULATE(
FILTER
(ALLEXCEPT(fSales;fSales[Service Plan 2]);
fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])
)
)
7 REPLIES 7
Super User

## Re: Cumulative sum of a measure

Can you show the structure of your tables? Or share the pbix if possible

Regular Visitor

## Re: Cumulative sum of a measure

@AlB here is the structure of the tables.

Super User

## Re: Cumulative sum of a measure

@chronis69

I cannot see the capture well. Could you share the pbix, if you don't have confidential data?

Super User

## Re: Cumulative sum of a measure

@chronis69

Are you using slicers as well or just the table/matrix visuals shown?

Are there relationships between your tables?

Regular Visitor

## Re: Cumulative sum of a measure

@AlB unfortunatelly, it does contain confidential data.

I have randomised the sales numbers but the pbix contains all sort of sensitive information that is quite difficult to remove.

No, I'm not using any slicers. just the two tables shown.

yes there is a relationship between dDates[Date] and fSales[OrderCreationDate].

see the ppt at the link below, I have a screenshot of the table structure that is super clear and the screenshot of the relationship present.

Community Support Team

## Re: Cumulative sum of a measure

Hi @chronis69,

Please check the following steps as below.

1. Create a date table and a calculated column in the date table.

`date = CALENDARAUTO()`

`YEAR = YEAR('date'[Date])`

2. Create a calcualted table.

`y = VALUES('date'[YEAR])`

3. Create the relationships between tables.

4. Create the measure to get the cumulative running total.

```Licenses_Running Total matrix =
VAR maxyear =
MAX ( 'y'[YEAR] )
VAR total =
CALCULATE (
FILTER ( ALL ( 'date' ), 'date'[YEAR] <= maxyear )
)
RETURN
IF ( ISBLANK ( total ), 0, total )

```

Then we can get the result as below.

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Cumulative sum of a measure

Frank thanks for the solution. Fiddling about I found another way that solved the problem. I replaced ALL with ALLEXCEPT.

this prevented the measure calculation from removing the implicit filter applied by [service plan 2] and hey !!! it worked.