Forum Discussion

LonePedersen's avatar
LonePedersen
Regular Visitor
8 years ago

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

Need Help

8 Replies

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    8 years ago

    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

    • LonePedersen's avatar
      LonePedersen
      Regular Visitor
      8 years ago

      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.

       

      • Abduvali's avatar
        Abduvali
        Skilled Sharer
        8 years ago

        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)

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

         


        Regards

        Abduvali