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 al...
Need Help
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

      • LonePedersen's avatar
        LonePedersen
        Regular Visitor
        8 years ago

        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