I simply cant figure out how to create a dynamic measure, that only counts start dates, where there is at lapse of at least 3 months since the last enddate.
I have a table with customers, that also include a "DateCaseStart" and a "CaseDateEnd" per line. The problem is that I only want a measure that can count a StartDate as a new startdate IF there is a timelapse of more than 3. months since last "CaseDateEnd" date.
Anyone that can figure this one out?
Link to example file: https://drive.google.com/open?id=1BV6-J8MuBup7jk_ZQwaH4eIA61-yrH40
Just to confirm are you only trying to display or count records when End Date 3 month greater than Start Date???
Not exactly. Last enddate has to be more than 3 month prior to next startdate.
I have given a couple of examples in the linked file. For instance Customer6:
There is a timelapse of 83 days (less than 3 months) between first "CaseDateEnd" (B) date and next "CaseDateStart" (A).
Therefore the measure shoulde only count the first "CaseDateStart" (10-05-2017) and not the second.
My problem is that i want a measure that can count start dates, but i want to filter the start dates, based on data from another row and column.....
Hopes it makes sense.
I think I got it to work using IF statements using 2 additional columns:
Check it out and see how you get on maybe someone will propose a better solution.
Thanks, looks exactly like what I am looking for. I just keep getting a circular reference. I think I may be skipping a step.
In the DAX function "New Start Date", there is a IF function containing a field named [New Date]. Is that at column ore a measure that I cant see?
I have tried this DAX formula, but it is here the circular reference occurs.
Sorry, mate its 3 columns you need I just omitted New Date one =oP
Thank you, this fixed the problem. But i didnt notice witch date was accepted as counts.
In the following table I have tried to make a column (like your table), that counts witch start dates, i want to include.
Customer 1: Both start dates is in the count because, there is a totalt count of 150 days between the last end date, and next start date (04-04-2017 to 01-09-2017).
Customer 6: Firstly I've added a additinal line. This table is just an example for a huge dataset I am working with, and here there woulde be severeal lines per customer. At first I thought I could use the "NewDate" Date you calculated earlyer. But I realised that when I use the solution in the big data set, the MAX date function woulde become a problem, because there can be a lot of start and end dates per customer.
In the folllowing table the measure/function has to count two new start dates from customer 6:
|Days between start and end|
Thank you for taking out time, to look at my problem.
I dont have a problem with the formula - The formula works perfectly.
The problem is, that it dosent count the correct start dates. If you se my table, I have a count of two for customer 1, because there is more than 3 months (or 90 days) between the end date (04-04-2017) and the next start date (01-09-2017). In your table there is only a count of one.
Sorry if I am explaining the problem incorrectly.