Forum Discussion

MiKeZZa's avatar
MiKeZZa
Post Patron
7 years ago

Running total per customer

I have a working running total per customer, but it gives problems when you have more than 1 customer.   The RT makes a cumulative number of months that a customer is with our company. As shown her...
Need Help
themistoklis's avatar
themistoklis
Community Champion
7 years ago

MiKeZZa

 

Try this formula:

 

CALCULATE( DISTINCTCOUNT(facttable[unique_counter] ),
FILTER(
ALLEXCEPT( Sheet1, Sheet1[customerid]),
'date'[date] <= MAX('date'[date])
))
MiKeZZa's avatar
MiKeZZa
Post Patron
7 years ago

Hi themistoklis

 

Uh.... I don't have Sheet1. Do you mean facttable?

 

That's also a thing I've tried, but the problem is that customerid is in the 'client' dim, and for date the same story.... They are not in the facttable. Of course I can bring it there with RELATED() but in that case I get this:

 

cumulatieve maanden per client =
CALCULATE( DISTINCTCOUNT('facttable'[unique_counter] ) ,
FILTER(
ALLEXCEPT( 'facttable', 'facttable'[customerid]),
'facttable'[datum] <= MAX('datum'[datum])))

But now my numbers go insane; they are skyhigh. What's wrong with this?