Hi @Natty.
As is often the case with Power BI, there are many ways to solve the same problem. Here’s the first solution I came up with:
The solution considers 4 possible cases to include an assignment into the selected “quarters”:
- the assignment start date is in the selected quarter(s)
- the assignment end date is in the selected quarter(s)
- both the assignment start date and end date are in the selected quarter(s)
- the assignment start date is before the start of the selected quarter(s) and the assignment end date is after the start of the selected quarter(s)
These 4 cases are contained in separate measures, then combined in a single [In Range] measure, and the [Assignments] table is filtered on this measure.
To enable this to work, I:
- marked [Calendar] table as a dates table
- turned off option for auto date/time
- renamed “dates” table to “Assignments” (to avoid confusion with “Calendar” (dates) table)
- set date format of Assignments[Assignment Start Date] and Assignments[Assignment End Date] columns to “dd-mmm-yyyy”
- adjusted start and end dates of [Calendar] table to 2014-01-01 and 2024-12-31 to cover the full period of assignment dates and eliminate (Blank)'s from the [Year, Quarter] slicer
- deleted relationship between Calendar[Date] and Assignments[Assignment Start Date]
- added filter to “Assignments” table visual: [In Range] = 1 only
This solution will fall down if non-contiguous quarters are selected, but hopefully will provide some help.
Greg
eDNA Forum - Quarters.pbix (5.9 MB)