Forum Discussion
Dynamic start date filter (Powerpivot)
Hi Abduvali,
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.
=if((Tabel1[NewStartDate]-Tabel1[CaseDateEnd])>=89,Tabel1[NewStartDate],Tabel1[CaseDateStart])
Regards
Lone
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 9Days 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