Hi Sam, I’ve sent you the example file by mail.
The problem occurs in the ISO-weeks 52 and 53. A year with 52 weeks has 52 * 7 = 364 ISO-days, a year with 53 weeks (like 2004, 2009, 2015, 2020 etc.) has 53 * 7 = 371 ISO-days. As an attachment you will find an Excel-file with different calculations.
I think that probably the solution for the FirstDateLTM-measure is:
If weeknumber = 52 and the max weeknumber of last year = 52 then FirstDate Weeknumber 1 of Current Year,
if weeknumber = 52 and the max weeknumber of last year = 53 then FirstDate Weeknumber 53 of Last Year,
if weeknumber = 53 then FirstDate Weeknumber 1 of Current Year, but correct me if i’m wrong.
I don’t know how to fit the probable solution into a Dax-measure. Can you help me?
Chrs,
Cor
Hulpmiddel week 52 en week 53.xlsx (79.1 KB)
PS:
The measure FirstDate PP LTM - 1 W goes also wrong, see 2005W52, the result is 26-12-2005 and this should be 20-12-2004…
Bit confused on this one.
Any reason why you can’t just use this?
FirstDate LTM -1W =
CALCULATE([FirstDate TW],
DATEADD( 'Calendar'[FullDate], -7, DAY ) )
Because I don’t want to know the date of 1 week prior, I want to know the firstdate of the prior week one year back:
To solve the challenge with week 53, maybe we should take the dates from week 52 and put them also on week 53. What do you think?
Ok sure,
How about this then
FirstDate LTM -1W =
CALCULATE(
CALCULATE([FirstDate TW],
DATEADD( 'Calendar'[FullDate], -7, DAY ) ),
DATEADD( 'Calendar'[FullDate], -1, YEAR ) )
Hi Sam,
Thanks for your reply Sam but unfortunately that isn’t the solution I’m looking for. For me it’s very complex.
See attachment, worksheet explanation.
I know, it looks like mathematics for me and I think also for you. I hope you will take the time to help me with DAX.
Thanks in advance,
Cor
Hulpmiddel week 52 en week 53.xlsx (82.9 KB)
Ok will review again. Sorry I haven’t really been understanding the true problem up to this point.
Ok this is kind of doing my head in.
I’ve look at this for a long time today and I’m up to a certain point and I’m hoping you can take it the next step.
It’s just difficult to get my head around every nuance with this calculation.
But…I do know why this was difficult and I have shown you how to fix it.
The key here is the difference between the calendar year and ISO year.
As these don’t align then the calculation in it’s current form was never going to work.
So what I worked out to do is create another calculated column in the calendar table for ISO year.
ISO Year = LEFT( 'Calendar'[CalendarYearWeekISO], 4 )
Then I have integrated this back into the formulas as it then works through correctly based on the context within the table in the report
I actually have most of your example correct, but then really don’t understand some of the others around the 53 weeks concept.
But this is how you solve what you need.
I think you should be able to work it out from here.
Attached
181010 YH Churn per week -1W Demo Data Sam (1).pbix (1.1 MB)
Just another note for future before I forget.
Can we please move new topic into new forum posts.
This one is a little different from the initial topic.
I like to break these out so they can be easily tracked and found at a later point.
Thanks
Thanks for your reply Sam, in future I will start a new topic.
I’ve changed a few measures:
MaxWeekNumberTY =
CALCULATE( MAX( 'Calendar'[WeekOfYearISO] );
ALL( 'Calendar' ); VALUES( 'Calendar'[ISO Year] ) )
MaxWeekNumberLY =
CALCULATE( [MaxWeekNumberTY]; DATEADD( 'Calendar'[FullDate]; -1; YEAR ) )
First Date Current Period =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )
RETURN
CALCULATE( [First Date - Current Week];
FILTER( ALL( 'Calendar' );
'Calendar'[WeekOfYearISO] = CurrentWeek + 1 &&
'Calendar'[ISO Year] = CurrentYear - 1 ) )
and I’ve made a new one:
FirstDate ISO Year = CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1);VALUES('Calendar'[ISO Year]))
As you can see in the next photo, we are still missing the First Date Current Period from 2004W52 and 2004W53 and the First Date Prior Period from 2004W53.
I understand that the result of the measure for 2004W52 and 2004W53 are blank because we haven’t got the weeks 2003W53 and 2003W54:
First Date Current Period =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )
RETURN
CALCULATE( [First Date - Current Week];
FILTER( ALL( 'Calendar' );
'Calendar'[WeekOfYearISO] = CurrentWeek + 1 &&
'Calendar'[ISO Year] = CurrentYear - 1 ) )
For the missing dates I want the result of the measure
FirstDate ISO Year = CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1);VALUES('Calendar'[ISO Year]))
How can I get the result of FirstDate ISO Year in the measures First Date Current Period and First Date Prior Period?
Chrs,
Cor
Attached
181015 YH Churn per week -1W Demo Data Sam en Cor.pbix (1.1 MB)
Okay getting closer I think. Will just have to look at this closer again, but don’t believe it’s to far from where things are at.
Can you wrap the initial function in an IF statement
Logic would work like this.
IF( Week Number is > 53, Initial Measure, Alternate Measure)
Something like this? Would it be suitable for those last remaining blanks?
Hi Sam,
I’ve changed the following measures:
First Date Current Period =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE('Calendar'[ISO Year])
VAR MaxWeekNumber = CALCULATE(max('Calendar'[WeekOfYearISO] ); ALL('Calendar') )
RETURN
IF(ISBLANK(CALCULATE([First Day of the week];
FILTER(ALL('Calendar');
'Calendar'[WeekOfYearISO] = VALUES('Calendar'[WeekOfYearISO]) + 1 && 'Calendar'[ISO Year] = VALUES( 'Calendar'[ISO Year]) -1) ));
CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1); VALUES('Calendar'[ISO Year]));
CALCULATE([First Day of the week];
FILTER(ALL('Calendar');
'Calendar'[WeekOfYearISO] = VALUES('Calendar'[WeekOfYearISO]) + 1 && 'Calendar'[ISO Year] = VALUES( 'Calendar'[ISO Year]) -1)) )
and
First Date Prior Period =
VAR CurrentWeek = SELECTEDVALUE('Calendar'[WeekOfYearISO])
VAR CurrentYear = SELECTEDVALUE( 'Calendar'[ISO Year] )
RETURN
IF(ISBLANK(CALCULATE( [First Day of the week];
FILTER( ALL( 'Calendar' );
'Calendar'[WeekOfYearISO] = CurrentWeek &&
'Calendar'[ISO Year] = CurrentYear - 1 ) ) );
CALCULATE(FIRSTDATE('Calendar'[FirstDateOfWeek]);
FILTER(ALL('Calendar'); 'Calendar'[WeekOfYearISO]=1); VALUES('Calendar'[ISO Year]));
CALCULATE( [First Day of the week];
FILTER( ALL( 'Calendar' );
'Calendar'[WeekOfYearISO] = CurrentWeek &&
'Calendar'[ISO Year] = CurrentYear - 1 )))
with the result:
I think that we’ve managed to fill the blank values, I can use these dates to calculate the churn by week.
Thanks for pushing me the right way, Sam! 
Greetings from The Netherlands,
Cor
Brilliant! Glad that we got there. Was a tricky one.
181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (689.2 KB)
Hi Sam,
See attachment above, I have some difficulties with the differences between the calculations of the “Lost Clients per Product” and the “Won Clients per Product”. The Grand Total of those 2 calculations are correct, if you add the several amounts per product, you will get another total. How can I solve this problem?
Furthermore I want to use the Lost Clients LTM, Lost Revenue Clients LTM, Won Revenue Clients LTM and the Won Clients LTM as a slicer. Can you tell me how I can fix that?
I’ve seen a lot of your videos on segmentation, grouping etc. but I can’t find the technique.
Thanks in advance,
With kind regards,
Cor
I’ve tried it with a calculated column in another datamodel but I’ve got the message that there was no memory left…
Customer Group =
SWITCH(TRUE();
NOT(ISBLANK([Total Sales Prior Period]) && ISBLANK([Total Sales LTM]));"Lost Clients";
ISBLANK([Total Sales Prior Period]) && NOT(ISBLANK([Total Sales LTM]));"Won Clients";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] < 0;"Lost Revenue";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] > 0;"Won Revenue";
NOT(ISBLANK([Total Sales Prior Period])) && NOT(ISBLANK([Total Sales LTM])) && [Diff. LTM vs PP] = 0;"Steady Revenue";
BLANK() )
The table YH-invoices of the other model contains more than 3,5 million rows.
In the meantime I’ve found a solution for the slicers with the churn-measures but I still have the miscalculations on the won and lost clients. Can you tell me what I’m doing wrong?
181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (721.6 KB)
Hi Sam,
I think the problem for Won Clients for 2015W27 it is in customer 30, product 15.
The customer is a customer with Won Revenue instead of Won Clients, the product is a “Won Product”, it wasn’t bought in the prior period. I don’t understand why this is. Can you explain it to me and help me to solve this problem?
181026 YH Churn per week -1W BM_PxQ_Invoices DEMO DATA V2.pbix (725.3 KB)
Hi Cor,
Sorry I’m a bit confused on this after a few posts in this thread.
Can we please start a new thread on this and be very specific about what you require. Point to specific formulas or results that you’re not sure about.
This has become a quite a complicated model and having to go through it every time over the last few months takes a lot of time.
I would like to be very specific around what you need here so I can look at just this and give you a solution.
Thanks
I think where the confusion lies is your talking about won client and lost clients then in the picture and when I download the file I see different calculations.
Total Sales LTM
Total Sales Prior Period
etc
Sorry it just need to be clearer for me. I want to be able to look at this and understand it pretty quick without having to revisit every detail about the model everytime I download it.
Appreciate it.
As mentioned let’s start a new thread on this.
Thanks
Sam
Sam, I will start a new thread on this. 