DAX-question - show the number of months that my customers needed to reach 1 Million €

[julianwi]

Hallo everyone!

I am fighting for a couple of hours with a DAX-calculation that originally sounded to be quite easy and I would be very happy about any help.

To quickly explain my "situation":

I do have a simple fact table with sales data. What I really want is quite easy to summarize: I want to create a measure that allows me to calculate the number of months that a certain customer "needed" to reach the treshhold of 1 Million Euros. That value is supposed to be shown on a customer dashboard that compares the top-customers. To start it would sufficient to hard-code the treshhold, later it should be replaced by a disconnected slicer.

My fact table is quite simple: Order Date, Amount and a calculated column called customer loyalty months that shows me for each sales transaction the number of months that this customer has been buying here. Furthermore I have a lookup-table for Customer Loyalty Months that is related to the loyalty month column of my fact-table.

My idea now was to create another calculated column in the fact table that creates some sort of running sum per customer based on the number of months:

Cumulated Revenue=Sumx(
Filter('Facts';Fact[Customer Loyalty Months] <= Earlier(Fact[Customer Loyalty Months]) && Fact[Customer ID]=Earlier(Fact[Kunde ID ]));
[Revenue])


This seems to work so far.

Now I try to create my measure that should show me the first month number where my treshhold (e.g. 1 million euros) has been reached. I tried a couple of things in DAX without success. I thought it would make to search for the lowest (min) month number where the treshhold has been reached:


[First Month Over Treshhold]=Calculate(
MINX(VALUES('Customer Loyalty Months');
'Customer Loyalty Months')[Month No]);
Filter(values('Customer Loyalty Months')[Month No]);
MAX(Fact[Cumulated Revenue])>1000000))

This however does not work - it always returns 1. It seems that my filter (MAX(Fact[Revenue]...) is not considered in the first MINX.

I could also imagine that I am misunderstanding the combination of X-Functions and Filter.

Regards,
julianwi

 

[julianwi]

Hallo.

I spent some more time reading blogs about similar problems and I think I came closer to the solution.

Using summarize I am able to count the number of months

[First Month Over Treshhold]=countrows(
filter(
summarize(
'Fact';
'Customer Loyalty Months'[Month No];
"Amount"; sum(Fact[Customer Loyalty Months])
);
calculate(max(Fact[Cumulated Revenue])) < 1000000))
+1

This only returns the wrong result when the treshhold is not reached at all for a customer - then it returns the entire number of months with revenue and not 0. I think I have to add somehow the criteria that 0 should be returned when the treshhold is never exceeded.

 

[miguel.escobar]

do you think that you could upload a copy of the workbook so I could take a look at it?

 

[julianwi]

Sure - here it can be downloaded: http://we.tl/TyRVNtlsW0 - I manually recreated that part of the data model that is relevant (due to the size and some confidental data in the original data set). Thanks in advance!

 

[miguel.escobar]

Hey!

So I went with the easy way and I used the Cumulative Column that you had in your fact table and taking in consideration that we're trying to find something static like an specific month that will never change it's a good way to go instead of using measures. The only downside of this is that we're adding a Column to the fact table which may take a big chunk out of your memory.

So, here's what I did.
1. I think I changed your Cumulative Revenue? (can't even remember now), added more rows to your fact table as well
2. Added a Calendar and a Customers Table
3. Within the Customer's Table I created some calc columns that look up in the fact table the following:
a. First Sales Date
b. First Date that the sales went over the threshold
c. The First Month that the sales went over the treshhold
4. Then I created a new pivot table to show you that information in a pivot table format

Here's the link to download the file https://dl.dropboxusercontent.com/u/54063091/pp test cumulated.xlsx

BTW, I used a different threshold but you can change it to whatever you want

Let me know if this helps

 

[miguel.escobar]

I had to update the file because of the EARLIER had a <= and it was only supposed to be a = sign. After that, I got a little too attached with the file and created a simple measure that will give you the exact date that they reached the threshold and then created another measure to see the amount of months that it took them to reach that threshold. If you'd like to use the measure instead, you could and that would save you tons of memory storage in your fact table.

 

[julianwi]

Hi miguel.escobar, I really like your solution
Measure 1:=MINX( FILTER( ALL(Calendar);
CALCULATE( SUM( fact[Revenue]);
DATESBETWEEN( Calendar[Date]; BLANK(); Calendar[Date])) >100000); Calendar[Date])

...especially because you even do not needed my cumulated revenue column. I will test it in my data set regarding the performance but it makes a very good impression!

 

[miguel.escobar]

glad you like it! You can even go further and use a disconnected table to create a new slicer for the threshold so you'd replace the ">100000" with ">MAX(DisconTable[Threshold])"

That would be REALLY cool and its not a hard thing to do.

 

[julianwi]

Yes, that disconnected slicer Option is why I prefer the second solution. The Performance however is problematic. In my data-set (about 500.000 fact lines) it takes about 30 seconds to refresh when I have a small dimension and gives an error for a big one (such as product dimension where I have a few hundred entries).

 

[miguel.escobar]

Yeah, the FILTER() and the MINX() loops are some formulas that when mixed together like in this example could have some performance issues but, when I tried the example with 700K rows it didn't have any issues.

What could happen is that your pivot table it's pretty loaded with fields and/or slicers and that might take a huge chunk of the CPU.

I re-saved that file to show the model with the 700K and it's actually not so bad just remember that you need to use a 64 bit computer when doing these type of measures. 32-bit would crash eventually.

Hope this helps!