I actually think the = is a small oversight. Don’t think you need it here.
Hi Sam,
I’m back from my holiday in Valencia-Spain and I’m struggling again with the formulas so please help me.
I want to compare different periods with each other, see photo below:
How can I put those periods in the different DAX-formulas? I understand the techniques of CALCULATETABLE, EXCEPT, INTERSECT etc. but I have some difficulties to put those periods into CALCULATETABLE.
Thanks in advance,
Cor
Have you got a date table setup?
You will need one for using these formulas and to run this type of analysis.
You can use the date table code within all the course and resources that are made available.
You will just need to make sure your quarters column aligns with the correct dates, then you can just use these formula as is.
Let me know.
Sam
Yes, Sam, I have a date table but I am struggling with the formula.
Won Customers LTM =
VAR CustomerLTM = CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
FILTER(ALL(‘Calendar’);
** what do I have to put here?**
VAR PriorCustomers = CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
FILTER(ALL(‘Calendar’ );
** what do I have to put here?
RETURN
COUNTROWS(
EXCEPT(CustomerLTM; PriorCustomers ) )
))
Can you help me?
Thanks in advance,
Cor
For the CustomerLTM seems to me that
DATESBETWEEN (
Calendar[FullDate],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
LASTDATE ( Calendar[FullDate] )
)
is the part that I missed but what do I have to use for the PriorCustomers?
I think I’ve found it:
Won Customers LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES(Invoices[Customer]);
DATESBETWEEN ('Calendar'[Date];
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES(Invoices[Customer]);
DATESBETWEEN('Calendar'[Date];
DATEADD(STARTOFQUARTER('Calendar'[Date]);-4;QUARTER);
DATEADD(ENDOFQUARTER('Calendar'[Date]);-1;QUARTER)))
RETURN
COUNTROWS(
EXCEPT(CustomerLTM;PriorCustomers))Ok yep that’s great. Yes, I think you certainly have the idea now.
Some sort of pattern but just slight adjustment required for the context of the calculation like you have done.
Chrs, Sam
Hi Sam,
180924 YH Churn per month -1 2003-2004.pbix (593.0 KB)
See attachment.
I am looking for a correct formula for Won Clients for 2004-2.
The context is Year-Month and the comparison is the number of customers last 12 months with the number of customers last 12 month for the prior month.
The correct answer for periode 2004-2 is 356 customers and I have made 3 different measures and the results of these 3 measures is 384 customers. I’m doing something wrong but I don’t know what. Maybe is the reason the leapday 29-02? Can you help me with the correct formula?
Another question:
Result 2003-1: the Total Clients Prior Period -1M and Lost Clients LTM give a result. What can I do to get rid of these results?
Chrs, Cor
Check this one out
Won3 Clients LTM =
VAR CustomerLTM = CALCULATETABLE(VALUES('2003-2004 Invoices'[Customer]),
DATESBETWEEN ('Calendar'[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('2003-2004 Invoices'[Customer]),
DATESBETWEEN('Calendar'[Date],
DATEADD(STARTOFMONTH('Calendar'[Date]),-13,MONTH),
LASTDATE( DATEADD('Calendar'[Date],-1,MONTH) )))
RETURN
COUNTROWS(
EXCEPT(CustomerLTM,PriorCustomers))
The original formula wasn’t actually finding the last date in January, it was going to the 29th
I just tested it (and that’s what I’ve circled). So I knew that wasn’t what you wanted.
You’ll see in the below formula I’ve adjusted it a little bit.
I always break things down like this to audit. It’s the best way.
Chrs
Hi Sam, thanks for your reply and the answer. I am still curious about your “Test”-measure. Can you tell me what the DAX-formula for “Test” is?
Thanks in advance,
Chrs, Cor
Another question:
Result 2003-1: the Total Clients Prior Period -1M and Lost Clients LTM gives both a result but there are no transactions in this period. What can I do to get rid of these results? 
Chrs, Cor
It was just this one
Test = DATEADD(LASTDATE(‘Calendar’[Date]),-1,MONTH)
I broke it out from the initial formula to see if it was getting the correct date bounds.
Something like this should do it
Total Clients Prior Period -1M =
IF( ISBLANK( [Total Clients LTM] ),
BLANK(),
CALCULATE([Total Clients LTM], DATEADD('Calendar'[Date],-1,MONTH)) )
Hi Sam,
How can the below Lost Customers measure be modified to look back 4 years instead of 1 year? The logic being sales in the last 4 years but not this year.
Customer Lost =
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )
RETURN
IF( ISBLANK( [Customer Sales TY] ),
BLANK(),
COUNTROWS( EXCEPT( CustomerLY, CustomersTY ) ) )
My current measure below is not producing the right results.
Lost Customers L4 =
var TY= VALUES('CompleteSalesSummary_Sales'[Rep])
var L4= CALCULATETABLE(VALUES('CompleteSalesSummary_Sales'[Rep]),
FILTER(ALL(DateTable),
DateTable[Date]>MIN(DateTable[Date])-1460 &&
DateTable[Date]<MIN(DateTable[Date])))
return COUNTROWS(EXCEPT(L4,TY))The formula looks ok to me from my review.
What is incorrect about the results?
Can you show what you are seeing and what you think it should be?
You could use this formula as the filter which might be more exact than just days
DATEADD(STARTOFMONTH('Calendar'[Date]),-4,YEAR),
LASTDATE( DATEADD('Calendar'[Date],-1,MONTH) )))
See how you go with this variation also
Sorry I am new to DAX, but where are you inserting that formula because I get blank answers when I try to put it in my measure. I know the New Customers measure is correct because the 281 checks out with a tedious excel process to arrive at new customers for 2018.
There are 2 issues with my Lost Customers L4:
- I am expecting the result to be 8 or very close, and am getting 707 for the year.
- Unlike the New Customers measure, this measure is returning 1204 for future months which is unexpected.
Any other ideas on capturing lost customers as the count of customers in 2014-2017 with sales and not in 2018?
Thanks in advance,
Adam
Ok thanks for this.
Lost customers is always a little tougher. Main reason for this is because how you determine a lost customer can vary widely.
Let’s walk through your existing formula and see if this is what you were wanting.
Lost Customers L4 =
var TY= VALUES('CompleteSalesSummary_Sales'[Rep])
var L4= CALCULATETABLE(VALUES('CompleteSalesSummary_Sales'[Rep]),
FILTER(ALL(DateTable),
DateTable[Date]>MIN(DateTable[Date])-1460 &&
DateTable[Date]<MIN(DateTable[Date])))
return COUNTROWS(EXCEPT(L4,TY))
Currently at every single row in your table you are comparing a list of customers who purchased in that particular month to a list of customers that purchased at anytime 4 years prior to that month.
Then by going EXCEPT(L4,TY) you are saying show me a list of customers who purchased in the last 4 years but didn’t purchase this month.
This is why you are getting quite large numbers.
Looking at this again it doesn’t really feel like this is what you’re going for with your lost customers.
What should the logic be?
Check out the lost customers formula here (and logic). It’s quite different. This might get you thinking about what you need a little more.
Here’s more detailed session I ran through new and lost customers also.
Let me know the outcome
Hi Sam,
After the calculations of the churn by year, quarter and month I’ve received the question to calculate the churn Last Twelve Months (LTM) by week…
For the same demo data ( ‘2003-2004 Invoices’) I have expanded the table Calendar with “FirstDateOfWeek” and “LastDateOfWeek”:
I’m struggling with the periods. Can you help me with the periods LTM and the prior period?
For example:
CalendarYearWeekISO: 2004-W02;
Weeks LTM = 2003-W03 - 2004-W02
Dates LTM = 13-01-2003 - 11-01-2004
Prior Period LTM-1W:
Weeks Prior Period = 2003-W02 - 2004-W01
Dates Prior Period = 06-01-2003 - 04-01-2004
I’ve tried to break the formulas to get the correct date bounds but I didn’t get it. For weekly comparison how do I have to break down the formulas?
Thanks in advance,
Cor
Hi Sam,
I’ve made some measures and they seem to do what I want:
My measures:
FirstDate LTM -1W =
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])
)
)
LastDate TW = LASTDATE ( 'Calendar'[LastDateOfWeek])
FirstDate PP LTM -1W =
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
'Calendar'[WeekOfYearISO] = max ('Calendar'[WeekOfYearISO])
)
)
LastDate PP LTM - 1W = DATEADD(LASTDATE('Calendar'[LastDateOfWeek]);-7;DAY)
Is this also your solution or do you have a better solution?
Can you also help me to put these measures to churn-measures like Won/Lost Clients and revenues?
Thanks in advance,
Chrs,
Cor
Hi Sam,
I have some difficulties with the year-breaks, especially the CalendarYearWeekISO 52 and 53 .
The measure
FirstDate LTM -1W =
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
'Calendar'[Year] = MAX('Calendar'[Year] ) -1 &&
'Calendar'[WeekOfYearISO] - 1= max ('Calendar'[WeekOfYearISO])
)
)
doesn’t seem to give the correct result so I’ve made another measure:
FirstDate LTM -1W TEST =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )
RETURN
CALCULATE([FirstDate TW];
FILTER(ALL('Calendar');
IF( CurrentWeek = 52;
'Calendar'[WeekOfYearISO] = CurrentWeek -51 && 'Calendar'[Year] = CurrentYear;
IF( CurrentWeek = 53;
'Calendar'[WeekOfYearISO] = CurrentWeek -52 && 'Calendar'[Year] = CurrentYear;
'Calendar'[WeekOfYearISO] = CurrentWeek + 1 && 'Calendar'[Year] = CurrentYear -1))))
and this measure is also not the solution:
Can you help me with the correct formula?
Thanks in advance,
Chrs,
Cor