Forum Discussion
Dynamic start date filter (Powerpivot)
Sorry, mate its 3 columns you need I just omitted New Date one =oP
- New Date = (CALCULATE(MAX(Sheet6[Start]),ALLEXCEPT(Sheet6,Sheet6[Customer])))
Regards
Abduvali
Hi Abduvali,
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:
- First timeperiod (01-01-2017 - 20-01-2017) has a count of 1 because it is the first startdate for the customer (no prior end date)
- Second timerperiod (10-05-2017 - 08-06-2017) has a count of 1 because timelapse between end date 20-01-2017 and next start date 10-05-2017) is 150 days.
- Third timeperiod (30-08-2017 - 31-12-2070) has a count of 0 because timelapse between the last end date 08-06-2017 and next start date 30-08.2017) is 83 days.
CustomerCaseDateStartCaseDateEndCountTotal 9
| Days between start and end | ||||
| Customer1 | 01-03-2017 | 04-04-2017 | 1 | |
| Customer1 | 01-09-2017 | 31-12-2070 | 1 | 150,00 |
| Customer2 | 05-05-2017 | 12-06-2017 | 1 | |
| Customer3 | 21-09-2017 | 22-09-2017 | 1 | |
| Customer3 | 21-09-2017 | 31-12-2070 | 0 | -1,00 |
| Customer4 | 25-08-2017 | 09-11-2017 | 1 | |
| Customer5 | 25-10-2017 | 31-12-2070 | 1 | |
| Customer6 | 01-01-2017 | 20-01-2017 | 1 | |
| Customer6 | 10-05-2017 | 08-06-2017 | 0 | 110,00 |
| Customer6 | 30-08-2017 | 31-12-2070 | 0 | 83,00 |
| Customer7 | 01-09-2017 | 31-12-2070 | 1 | |
| Customer8 | 28-06-2017 | 30-07-2017 | 1 | |
| Customer8 | 12-09-2017 | 31-12-2070 | 0 | 44,00 |
Thank you for taking out time, to look at my problem.
Regards Lone
See table below all work as expected, I think you got your Count dates formula wrong make sure you didn't mix up between < or > signs in the formula.
Hi Abduvali,
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.
Regards
Lone