Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to insert a Year Slicer that filters the End Date column based on the selected year AND also displays records with EndDates that are 10 days after the end of the year only if YEAR(TimeOff[EndDate]) <> YEAR(TimeOff[StartDate]).
So what I have is a Date Table and a TimeOff Table, where the relationship is: Date[Date] with TimeOff[EndDate] (one to many)
Example:
For this record (where the dates are TimeOff[StartDate] & " - " & TimeOff[EndDate])
I want to see this record both when I select 2023 and 2022 in the YEAR in the slicer. It should be included when I select 2022 because the EndDate (circled in red) meets two conditions: 1) YEAR(TimeOff[EndDate]) <> YEAR(TimeOff[StartDate]);
2) Timeoff[StartDate]>=DATE(SelectedYear, 1, 1) - 10
Problem: I don't know how to create the year slicer so that when I select 2022 it displays records like the example (that have a EndDate year = 2023 but they are 10 days after the end of the selected year (2022) and the start date year is not equal to the end date year). With the current Slicer it is only appearing when I select 2023.
If you could help me it wil be awesome!
Thanks 🙂
@Anonymous Get Previous year from selected year
@mariaesosa You'll need to construct a measure that incorporates ALL, something like:
Days Off =
VAR __Year = MAX('Table'[Year])
VAR __MaxEndDate = DATE(__Year,12,31) + 10
VAR __Result = SUMX(FILTER(ALL('Table'), YEAR([EndDate]) = __Year || ( YEAR([EndDate]) = __Year + 1 && [EndDate] <= __MaxEndDate), [Some Column])
RETURN
__Result
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |