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 here it works great: 

Customer 1533 leaves the company in 201712, so in 201801 you only see 1528.

 

I except/want the RT to make an average of this when I take a view per yearmonth, without selecting the customer. But this gives:

 

As you can see 201801 still counts the number of cumulative months of customer 1533; so thats the reason the measure gives 377 in stead of 189.

 

So it ignores the customer when you don't select it. I really can't force him to don't do this. Somebody can tell me how to fix this?

 

The DAX-codes:

 

cumulative months =
CALCULATE( DISTINCTCOUNT(facttable[unique_counter] ),
FILTER(
ALL('date'[date]),
'date'[date] <= MAX('date'[date])
))
 
unique customers = DISTINCTCOUNT('facttable'[customerid])
 
average cumulative length in months = divide([cumulative months], [unique customers])

 

 

Need Help

10 Replies

  • 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?
  • MiKeZZa's avatar
    MiKeZZa
    Post Patron
    7 years ago

    My PBIX (anonymized) is here: https://ufile.io/0btas

     

    What you see is that when somebody isn't in the dataset anymore in a specific month he does still count in the next month(s). That's not as intended to be.

     

    See client 1531; he is gone in after 201803. From there on I except 0 in april, but it does count 456 for every month. So it is doing what a normal RT needs to do, but I want it to go to 0 from april.

    • themistoklis's avatar
      themistoklis
      Community Champion
      7 years ago

      MiKeZZa

       

      I think you should modify the X Axis.

       

      Create a new year-month column (on fact_per_month table) which comes from datum and not date and then add it to the chart

      Column = FORMAT(fact_per_month[datum], "MMM YYYY")
      • MiKeZZa's avatar
        MiKeZZa
        Post Patron
        7 years ago

        This seems to work. Why is this?

         

        I can imagine that the datedim goes untill 201803 because of the longest during customer and now it's record per record so its 201803 for 1531 and 201712 for 1533. But what about things as 'make a good model with a starschema' and so on?

         

        How can I tell my powerusers who Analyze in Excel that they don't have to use the datedim here but the datefields from the fact (which is normally hidden because of that I make the measures in a separate table)?