Cumulative sum of a measure

[Anonymous]

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 

Licenses_sum = SUM(fSales[Licenses_sold])

Licenses_Running Total =

CALCULATE(

[Licenses_sum];

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. 

I apprecciate your help.

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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 (
SUM ( fSales[Licenses_sold] ),
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

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

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 (
SUM ( fSales[Licenses_sold] ),
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

[Anonymous · Accepted solution]

@v-frfei-msft, @AlB

thanks for your help.

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.

Licenses_Running Total =

CALCULATE(

[Licenses_sum];

FILTER

(ALLEXCEPT(fSales;fSales[Service Plan 2]);

fSales[OrderCreationDate]<=MAX(fSales[OrderCreationDate])

)

)

[AlB]

Hi @Anonymous

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

[Anonymous]

@AlB here is the structure of the tables.

 

[AlB]

@Anonymous

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

[Anonymous]

@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.

https://drive.google.com/file/d/1Wr9l6dg5rkNlzochfAT7vZLptw0HGZxY/view 

[AlB]

@Anonymous

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

Are there relationships between your tables?