DAX: Cumulative Total Applying filters

[glaso-is]

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!
   

[Greg_Deckler]

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. 

[glaso-is]

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

[popov]

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

[MKY]

Function 'ALLSELECTED' made lot of difference in one of the database I was working with. I was using 'ALL' but when replaced with 'ALLSELECTED' the issue was resolved. Thanks a lot.

[Anonymous]

Thank you!!!

[glaso-is]

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!

[popov]

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.