Running total per customer

[MiKeZZa]

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

[MiKeZZa]

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]

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

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

[themistoklis]

@MiKeZZa

Basically you need to understand how PowerBi deals with this kind of relationships between the tables.

Date table is just a superset (manually specified date ranges) but the actual data is on fact_per_month field which is the one that will give you the information that you want.

Date table normally gives you the option to retrieve date and time related data. These data could have also be taken from fact_per_month table without using the Date table at all.

I hope it makes sense. 

[MiKeZZa]

Yes this make sense. At all! This was also what I ment to say. I think I understand the model good enough to understand that the datedim gets all the values that are available in the fact, when you take a date month in the fact t's based on per customer. So that's 100% clear.

But this is a rupture to the statement that PowerBI models must be based on a traditional starschema by favor. And what about special things where a date dim is ment for (lets say; last 13 months, is current year, is current accounting year, quarter and so on).... These kind of reportquestions make a date dim useless I think. That's what surprires me very; the concept is clear to me.

[v-cherch-msft]

Hi @MiKeZZa

You may add a visual level filter as below:

Regards,

Cherie

[themistoklis]

@MiKeZZa

Try this formula:

CALCULATE( DISTINCTCOUNT(facttable[unique_counter] ),
FILTER(
ALLEXCEPT( Sheet1, Sheet1[customerid]),
'date'[date] <= MAX('date'[date])
))

[MiKeZZa]

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]

@MiKeZZa

Is it possible to share the file with us?

Mask any sensitive data

[v-cherch-msft]

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