cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LonePedersen
Regular Visitor

Dynamic start date filter (Powerpivot)

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?

 

CustomerCaseDateStartCaseDateEnd

Customer1

01-03-2017

04-04-2017

Customer1

01-09-2017

31-12-2070

Customer2

05-05-2017

12-06-2017

Customer3

21-09-2017

22-09-2017

Customer3

21-09-2017

31-12-2070

Customer4

25-08-2017

09-11-2017

Customer5

25-10-2017

31-12-2070

Customer6

10-05-2017

08-06-2017

Customer6

30-08-2017

31-12-2070

Customer7

01-09-2017

31-12-2070

Customer8

28-06-2017

30-07-2017

Customer8

12-09-2017

31-12-2070

 

Link to example file: https://drive.google.com/open?id=1BV6-J8MuBup7jk_ZQwaH4eIA61-yrH40

8 REPLIES 8
Abduvali
Skilled Sharer
Skilled Sharer

Hi @LonePedersen,

 

Just to confirm are you only trying to display or count records when End Date 3 month greater than Start Date???

  •  Like the record below is ok right?

    Customer1

    01-09-2017

    31-12-2070

     

Regards

Abduvali

Hi @Abduvali,

 

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.

 

Udklip.PNG

@LonePedersen

 

I think I got it to work using IF statements using 2 additional columns:

 

  1. this checks for the newest Start Date
    1. New Start Date = if((Sheet6[New Date]-Sheet6[End])>=89,Sheet6[New Date],Sheet6[Start])
  2. And the second column will let you count the dates that are greater than 3 month
    1. Count Dates = if((if(Sheet6[End]>Sheet6[New Start Date],0,DATEDIFF(Sheet6[End],Sheet6[New Start Date],DAY)))=0,0,1)

Capture36.PNG

Check it out and see how you get on maybe someone will propose a better solution.

 


Regards

Abduvali

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])

 

UdklipNY.PNG

 

Regards

 

Lone

@LonePedersen

 

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
Customer101-03-201704-04-20171 
Customer101-09-201731-12-20701150,00
Customer205-05-201712-06-20171 
Customer321-09-201722-09-20171 
Customer321-09-201731-12-20700-1,00
Customer425-08-201709-11-20171 
Customer525-10-201731-12-20701 
Customer601-01-201720-01-20171 
Customer610-05-201708-06-20170110,00
Customer630-08-201731-12-2070083,00
Customer701-09-201731-12-20701 
Customer828-06-201730-07-20171 
Customer812-09-201731-12-2070044,00
 

 

Thank you for taking out time, to look at my problem.

 

Regards Lone

@LonePedersen,

 

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.

Capture37.PNG

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors