Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Has some dax function to filter dates as follows for 2 departments:
For the X department I need to filter dates before or the same as today!
For Y department I need to filter dates before or equal to today plus 2 days, excluding Saturday / Sunday for both situations.
Hi
I made a small sample with a calendar table containing dates from 2017-03-20 until 2017-04-06.
I then added a few columns:
Returns 1 if date is before or the same as today; otherwise 0
IsInPast = IF('Calendar'[Date].[Date] <= TODAY(),1,0)
Calculates the day of week:
DayOfWeek = WEEKDAY('Calendar'[Date])
Returns 1 if date is before or equal to today plus 2 days; otherwise 0
IsBeforeEqualDayAfterTomorrow = IF(DATEADD('Calendar'[Date].[Date],-2;DAY) <= TODAY(),1,0)
Returns 1 if date is before or equal to today plus 2 days AND is not Saturday or Sunday; otherwise 0
IsBeforeEqualDayAfterTomorrow2 = IF(
DATEADD('Calendar'[Date].[Date],-2,DAY) <= TODAY()
&& 'Calendar'[DayOfWeek] <> 7
&& 'Calendar'[DayOfWeek] <> 1,1,0)
At the time of writing it is March 28, resulting in this table:
You can now use these columns for slicers or your visual filters.
Hope this helps
JJ
Good suggestion, but I want the result using the CALCULATE / FILTER or other functions, however I am not hitting
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |