cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nielsvanbruggen Frequent Visitor
Frequent Visitor

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 looks something like this (the last columns show what I would like to accomplish):

 

 

ClientIDProductIDStartdateEnddate StartDate(What I want)Enddate (What I want)
1231  1-1-17 28-2-17 1-1-17 30-6-17
231  1-3-1731-5-17 1-1-1730-6-17
231  1-6-1730-6-171-1-1730-6-17
231 1-9-1730-10-171-9-17 31-12-17
231  1-11-1731-12-171-9-17 31-12-17
231  1-1-18 1-9-1731-12-17
      
      

 

In Excel this is quite easy, but I don't know how to accomplish it in Power BI.

 

Can someone help me?


Thanks!! 

7 REPLIES 7
thiagopinho Frequent Visitor
Frequent Visitor

Re: Filter rows where difference between dates is lower than 31 days

Hey, i didn't get the idea, is possible to detail it?

Microsoft v-yulgu-msft
Microsoft

Re: Filter rows where difference between dates is lower than 31 days

Hi @nielsvanbruggen,

 

"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."

 

What does startdate of another row mean? How do determine which startdate is used to compare with enddate in current row?

 

What is your desired output? Do you want a new table which has been filtered? Or do you want to add a new calculated column in source table?

 

Please provide more description about [StartDate(What I want)] and [Enddate(what I want)]. I don't understand how to get values like this.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ManNVSM Member
Member

Re: Filter rows where difference between dates is lower than 31 days

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 Frequent Visitor
Frequent Visitor

Re: Filter rows where difference between dates is lower than 31 days

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! 🙂

ManNVSM Member
Member

Re: Filter rows where difference between dates is lower than 31 days

 

@nielsvanbruggen

Can you copy your formular in your excel please?

 

Regards,

ManNVSM.

nielsvanbruggen Frequent Visitor
Frequent Visitor

Re: Filter rows where difference between dates is lower than 31 days

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

nielsvanbruggen Frequent Visitor
Frequent Visitor

Re: Filter rows where difference between dates is lower than 31 days

Hi!


Yes, I can. 

 

What I do in Excel is that I make additional columns to "group" the existing table. I use the following formulas:

 

Columns F, G, H and I make a new grouped table (how I want it) with only data in rows 3, 5 and 7. 

 

 BCDEFGHI
 ClientID ProductStartdateEnddateStartdate (new)Startdate (new 2)Enddate (new)Product (new)ClientID (new)
11230011-1-1731-1-17=C1 --> 1-1-17=IF(E1=E2;"";E1) --> ""=IF(E1=E2;"";D1) --> ""=IF(E1=E2;"";B1) --> ""=IF(E1=E2;"";A1) --> ""
21230011-2-1728-2-17

=IF(AND(

 A2=A1;B2=B1;C2<(D1+31));

 E1; C1) --> 1-1-17

=IF(E2=E3;"";E2) --> ""=IF(E2=E3;"";D2) --> ""=IF(E2=E3;"";B2) --> ""=IF(E2=E3;"";A2) --> ""
31230011-3-1731-3-17

=IF(AND(

 A3=A2;B3=B2;C3<(D2+31));

 E2; C2) --> 1-1-17

=IF(E3=E4;"";E3) --> 1-1-17=IF(E3=E4;"";D3) --> 31-3-17=IF(E3=E4;"";B3) --> 001=IF(E3=E4;"";A3) --> 123
41230011-8-1730-8-17

=IF(AND(

 A4=A3;B4=B3;C4<(D3+31));

 E3; C3) --> 1-8-17

=IF(E4=E5;"";E4) --> ""=IF(E4=E5;"";D4) --> ""=IF(E4=E5;"";B4) --> ""=IF(E4=E5;"";A4) --> ""
51230011-9-1731-9-17

=IF(AND(

 A5=A4;B5=B4;C5<(D4+31));

 E4; C4) --> 1-8-17

=IF(E5=E6;"";E5) --> 1-8-17=IF(E5=E6;"";D5) --> 31-9-17=IF(E5=E6;"";D5) --> 001=IF(E5=E6;"";A5) --> 123
6123

002

1-1-1731-1-17

=IF(AND(

 A6=A5;B6=B5;C6<(D5+31));

 E6; C6) --> 1-1-17

=IF(E6=E7;"";E6) --> ""=IF(E6=E7;"";D6) --> ""=IF(E6=E7;"";B6) --> ""=IF(E6=E7;"";A6) --> ""
712300215-2-1731-3-17

=IF(AND(

 A7=A6;B7=B6;C7<(D6+31));

 E7; C7) --> 1-1-17

=IF(E7=E8;"";E7) --> 1-1-17=IF(E7=E8;"";D7) --> 31-3-17=IF(E7=E8;"";D7) --> 002=IF(E7=E8;"";D7) --> 123
81230021-9-1731-9-17

=IF(AND(

 A8=A7;B8=B7;C8<(D7+31));

 E8; C8) --> 1-9-17

    

 

Hopefully you can help me with this one!

 

Thanks!

Niels

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors