Forum Discussion

nielsvanbruggen's avatar
nielsvanbruggen
Regular Visitor
8 years ago

Filter rows where difference between dates is lower than 31 days

Hello,   have a dataset where I want to filter out (or combine) rows when the difference between the enddate of this row and the startdate of another row is lower than 31 days.   The dataset look...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Dear nielsvanbruggen,

 

You can create a new column, using this formular:

  • StartDate(What you want) = MIN (Table[Startdate], ALLEXCEPT(Table, ClientID))
  • EndDate(What you want) = MAX(Table[Enddate], ALLEXCEPT(Table, ClientID))

 

And all the same ClientID will have same min startDate and max Endate.

 

Rregards,

ManNVSM.

  • nielsvanbruggen's avatar
    nielsvanbruggen
    Regular Visitor
    8 years ago

    Thanks for all the reactions!

     

    I did create new columns with the DAX-formulas you presented. The problem is that when the enddate in one row is more than 31 days before the startdate of the next row, I want the formula to keep the startdate of that row and use it for the rows to come (for the combination clientid and product. 

     

    Is this clear enough? I hope so! :)

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

       

      nielsvanbruggen

      Can you copy your formular in your excel please?

       

      Regards,

      ManNVSM.

      • nielsvanbruggen's avatar
        nielsvanbruggen
        Regular Visitor
        8 years ago

        Yes, of course!

         

        To create the start and end dates I want I use this formula in Excel: 

         - "Startdate new": 

                             Row 1: "=Startdate row 1"

                             Row 2-.. "=IF(

                                                   AND(

                                                            ClientID row 2 = ClientID row 1;

                                                            Startdate row 2= Startdate row 1;

                                                            Startdate row 2 < (Enddate row 1 + 31));

                                                                                                                             Startdate new row 1 ; Startdate row 2 )

         - "Startdate new 2"

                           "=IF(Startdate new row 1 = Startdate new row 2;

                                                                                                         ""; Startdate new row 1)

        - "Enddate new" 

                           "=IF(Startdate new row 1 = Startdate new row 2;

                                                                                                         ""; Enddate row 1)

         

         

        The I perform the steps of "Startdate new 2' and "Enddate new" also for "Product new" and "ClientID new". These steps all together make that I "group" these rows in a new table. 

         

        I really want to do this in Power BI, but I don't know how to do this. Maybe in Power Query?? 

         

        Thanks all! 

         

        Niels