cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
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.

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] )
)
)

Anonymous
Not applicable

Thank you!!!

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!

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors