Forum Discussion

MiKeZZa's avatar
MiKeZZa
Icon for Post Patron rankPost 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
Icon for Community Champion rankCommunity 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
    Icon for Post Patron rankPost 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?
    • themistoklis's avatar
      themistoklis
      Icon for Community Champion rankCommunity Champion
      7 years ago

      MiKeZZa

       

      Is it possible to share the file with us?

       

      Mask any sensitive data

      • v-cherch-msft's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi MiKeZZa

         

        It's better that if you could share some data which could reproduce your scenario and your desired output so that we could help further on it.

         

        Regards,

        Cherie