cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## DAX: Cumulative Total Applying filters

Hi,

I was trying to get the cumulative total from a filtered dataset selecting some years in the slicer, but I'm always obtaining the cumulative from the whole dataset only for the selected years.

For example:

Total          Cumulative Total
2010      81\$                     81\$

2011      83\$                    164\$

2012      90\$                     254\$

If I select 2011 and 2012 I would like to obtain the cumulative only for the selected years and not for the entire dataset.

Total         Filtered Cumulative Total

2011      83\$                    83\$

2012      90\$                   173\$

I have tried the following DAX formulas, but always is returning the cumulative taking the whole dataset.

`Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLEXCEPT('Calendar';'Calendar'[Date].[Year]); 'Calendar'[Date] <= MAX('Calendar'[Date])))`

`Cumulative Total Applying filters = CALCULATE([Total Sales]; FILTER(ALLSELECTED('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date])))`

Any idea?  Please find attached an example of PowerBI where I've tried these formulas. DAX Example

Thanks!

6 REPLIES 6
Highlighted
Super User IV

Is "Total" a column in your example a column? The default SUM aggregation of the column will work in your case for the data you provided.

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

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Resolver III

Hi, @glaso-is
If I correct understud, you need to use ALLSELECTED() function, something like this:

Cumulative Total Applying filters :=
CALCULATE (
[Total Sales];
FILTER (
ALLSELECTED ( 'Calendar' );
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)

Highlighted
Frequent Visitor

"Total" is a measure, a simple of a sum of a column. The problem is the cumulative is always taking the previous years, ignoring the slicer.  Please find a link of PBIX file where I have reproduced my problem:

https://www.dropbox.com/s/rurt5vp7ddbcs87/Test%20Cumulative%20Total%20Applying%20filters.pbix?dl=0

Thanks.

Highlighted
Frequent Visitor

Hi @popov,

The DAX formula that you commented is one of Ithe formulas that I'd tried as I mentioned in my first post, but the problem is always adding the previous years. For example, if I have a dataset with data for 2010, 2011,2012,2013 and I select in the slicer 2011 and 2013 my expectations is to obtain the following:

2011: Sum Total of 2011
2013: Sum Total of 2011 and Total 2013

However, using the formulas that I have posted in my first post I obtain the following results:

2011:  Sum Total 2010 and Total 2011

2013:  Sum Total 2010, Total 2011, Total 2012 and Total of 2013

Here, PBIX file where I have reproduced the problem:

https://www.dropbox.com/s/rurt5vp7ddbcs87/Test%20Cumulative%20Total%20Applying%20filters.pbix?dl=0

Thanks!

Highlighted
Resolver III

Hi, @glaso-is
I use formula in my previos post and getting correct result. See screenshot. In your file, formula of Cumulative Total Applying filters is not correct.

Highlighted
Regular Visitor

Thank you!!!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors