Forum Discussion
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! :)
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
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.
A B C D E F G H I ClientID Product Startdate Enddate Startdate (new) Startdate (new 2) Enddate (new) Product (new) ClientID (new) 1 123 001 1-1-17 31-1-17 =C1 --> 1-1-17 =IF(E1=E2;"";E1) --> "" =IF(E1=E2;"";D1) --> "" =IF(E1=E2;"";B1) --> "" =IF(E1=E2;"";A1) --> "" 2 123 001 1-2-17 28-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) --> "" 3 123 001 1-3-17 31-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 4 123 001 1-8-17 30-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) --> "" 5 123 001 1-9-17 31-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 6 123 002
1-1-17 31-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) --> "" 7 123 002 15-2-17 31-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 8 123 002 1-9-17 31-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