Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mariaesosa
New Member

Year Slicer that also gives you records outside the selected year if they meet two conditions

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])

mariaesosa_0-1680038972139.png

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

mariaesosa_1-1680039053761.png

mariaesosa_2-1680039216794.png

 

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 

 

 

1 REPLY 1
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.