I need to calculate which QUARTERS this row of data is included in so I can then slice the data and measures using the slicer.
As shown above - the date range can cover multiple QUARTERS, so I would like this row to be calculated in each quarter based on the slicer. Date query.pbix (5.9 MB)
I’m stumped how to work this out using time intellegence DAX?
Can anyone help?
I have uploaded a very basic example.
Many thanks for your assistance in advance.
Natalie
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)
