The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
This is a seemingly simple question where I want to know how many IDs have start dates in the current filter context that are within X days (parameter) of any previous endDate. Sample data are shown below with some annotations. Both startDate and endDate are connected to the same date table, with startDate being the active relationship.
Assuming the X days parameter is set at 200 days, for each set of rows for a given ID, I would like to identify rows where a StartDate begins within 200 days of a previous EndDate. These cells are highlighted below:
Those highlighted cells have a StartDate that begins within 200 days of a previous EndDate.
With that, I would be able to count the number of returns within X days per ID:
or the number of returns within X days by date:
To approach this, I began with a table of Dates[Year] and 'Sample'[ID], so the filter context is the year of startDate. Then I need to compare a) a list of IDs with startDates in the current filter context to b) a list of IDs where any previous endDate is within X days of the current startDate. Specifically, I want to filter the list in a to only include the IDs that are in b.
I wasn't exactly sure how to adjust the filter context for b above (the IDs that have endDates within X days of the current startDate). I obviously need to activate the inactive relationship between 'Dates'[Date] and 'Sample'[EndDate], i.e.,
How should i approach this instead?
'Sample':
ID | StartDate | EndDate |
1 | 1/1/2018 | 6/6/2019 |
1 | 1/1/2020 | 6/6/2021 |
1 | 1/1/2022 | 12/1/2022 |
2 | 12/1/2017 | 3/15/2018 |
2 | 8/15/2018 | 3/15/2019 |
2 | 1/1/2020 | 6/1/2020 |
3 | 4/15/2017 | 12/1/2018 |
3 | 8/15/2019 | 1/1/2020 |
3 | 1/1/2021 | 6/1/2021 |
4 | 1/1/2021 | 12/1/2022 |
5 | 3/15/2018 | 7/20/2018 |
5 | 1/1/2019 | 7/1/2019 |
5 | 1/1/2021 | 1/1/2022 |
6 | 6/1/2017 | 6/1/2018 |
6 | 1/1/2022 | 6/1/2022 |
7 | 2/1/2019 | 10/1/2019 |
7 | 6/1/2022 | 11/1/2022 |
8 | 10/1/2019 | |
9 | 11/1/2022 | 12/1/2022 |
10 | 12/1/2022 |
@amitchandak I made some clarifications in the original post. hopefull it is more clear now. thank you for your reply.
@huffd , not very clear, You can get the last end date with new columns
maxx(filter(Table, [ID] =earlier([ID]) && [end date] < earlier([end Date]) ), [end date])
this can be used to get diff
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
User | Count |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |