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

 

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