Cumulative Count Not working as expected

[SergioTorrinha]

Hi all,

My data model is composed , amongst other tables, by an Orders table (facOrder) and a dimensional customer table (dimCustomer).
These 2 tables are linked by customerID key.

I wrote a DAX metric to retreive the cumulative total of customers that had, at least one order registered in the facOrder table, like this:

CumUsers = 
// metric to calculate the cumulative number of customers since theyr first order


VAR CustomerOrders = 
                    SUMMARIZE(
                                facOrders,
                                facOrders[CustomerID],
                                "FirstOrderDate",
                                MIN(facOrders[CreationDate])
                            )

VAR SelDate =  MAX( dimDates[DateID] )

VAR CountCustomers = COUNTROWS( FILTER( CustomerOrders, [FirstOrderDate] <= SelDate  ) )
                    
RETURN

IF( CountCustomers  = BLANK(), 0 , CountCustomers  )

What's happening, is that this metric is not retreiving an acumulated total, but is, instead, calculating the total for a given selected (by the filter context) month. And, btw, I'm trying to plot a chart that renders monthly figures.

Before you ask for data: i'm working with sensitive data, so if you want something to test, please use  AdventureWorks database .

Thanks in advance 🙂

[amitchandak · Accepted solution]

@SergioTorrinha , Not very clear.

Try like

calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate])))

or

calculate(counrows(facOrders), filter(allselcted(facOrders) , facOrders[CreationDate] <=Max(facOrders[CreationDate]) && facOrders[CustomerID] <=Max(facOrders[CustomerID])))

[SergioTorrinha]

Hi @amitchandak !

Thank you for your feedback.
I managed to make it work properly, with the following adaptation of your first DAX:

a_test = 
        CALCULATE(
                DISTINCTCOUNT(facOrders[CustomerID]), 
                FILTER( ALLSELECTED(facOrders) , facOrders[CreationDate] <= Max(dimDates[DateID])) //Max(facOrders[CreationDate]))
                )

Thank you for your help! 🙂