Forum Discussion
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):
| ClientID | ProductID | Startdate | Enddate | StartDate(What I want) | Enddate (What I want) |
| 1 | 231 | 1-1-17 | 28-2-17 | 1-1-17 | 30-6-17 |
| 1 | 231 | 1-3-17 | 31-5-17 | 1-1-17 | 30-6-17 |
| 1 | 231 | 1-6-17 | 30-6-17 | 1-1-17 | 30-6-17 |
| 1 | 231 | 1-9-17 | 30-10-17 | 1-9-17 | 31-12-17 |
| 1 | 231 | 1-11-17 | 31-12-17 | 1-9-17 | 31-12-17 |
| 1 | 231 | 1-1-18 | 1-9-17 | 31-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
Hey, i didn't get the idea, is possible to detail it?
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.
Best regards,
Yuliana Gu
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.
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! :)
