Forum Discussion

SergioTorrinha's avatar
SergioTorrinha
Resolver II
5 years ago
Solved

Cumulative Count Not working as expected

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 🙂

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      SergioTorrinha
      Resolver II
      5 years ago

      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! 🙂