Forum Discussion

glaso-is's avatar
glaso-is
Frequent Visitor
8 years ago

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...
Need Help
popov's avatar
popov
Icon for Resolver III rankResolver III
8 years ago

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

glaso-is's avatar
glaso-is
Frequent Visitor
8 years ago

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's avatar
    popov
    Icon for Resolver III rankResolver III
    8 years ago

    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.