Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to show 11 days of the week in matrix visual- 3 wekdays past today, today and 7 days after today as below. Also, skipping the weekdays and need the day associated with it. I have a calender table that has a column for weekday as 1 for weekday and 0 for weekend. How can I do that? If not possible in Matrix then I can go for table too.
Solved! Go to Solution.
Hi
How about you create a calculated column in which you calculate if each date is in the range you want to display and use that column as a filter? You have to make sure though that the calendar table will be loaded / calculated each day. Here is the formula I came up with for the new column. You might tweak it to make sure it also works if the start or end of the range falls on a Saturday.
Is In Range =
VAR _Today = TODAY()//for test purposes: enter the date to test with: DATE(2022,3,14)
//Check how many weekend-day are ahead: check 11 days ahead, as there can be 4 weekend days max
VAR _WeekendDaysAhead = COUNTROWS(FILTER('Calendar','Calendar'[Weekday]=0 && 'Calendar'[Date] <= _Today+11 && 'Calendar'[Date]>_Today))
//Calculate the last day in the range: if there 2 weekend days ahead, add 9 days, otherwise 11 days.
//Please verify if this logic works if there are 3 weekend days ahead
VAR _LastDayInRange = IF(_WeekendDaysAhead = 2,_Today+9,_Today+11)
VAR _WeekendDaysPast = COUNTROWS(FILTER('Calendar','Calendar'[Weekday]=0 && 'Calendar'[Date] >= _Today-5 && 'Calendar'[Date]< _Today))
VAR _FirstDayInRange = IF(_WeekendDaysPast = 0, _Today-3,_Today-5)
RETURN IF('Calendar'[Date] >= _FirstDayInRange && 'Calendar'[Date] <= _LastDayInRange && 'Calendar'[Weekday] =1,1,0)
The resulting calendar table:
Hope this helps.
JJ
Hi
How about you create a calculated column in which you calculate if each date is in the range you want to display and use that column as a filter? You have to make sure though that the calendar table will be loaded / calculated each day. Here is the formula I came up with for the new column. You might tweak it to make sure it also works if the start or end of the range falls on a Saturday.
Is In Range =
VAR _Today = TODAY()//for test purposes: enter the date to test with: DATE(2022,3,14)
//Check how many weekend-day are ahead: check 11 days ahead, as there can be 4 weekend days max
VAR _WeekendDaysAhead = COUNTROWS(FILTER('Calendar','Calendar'[Weekday]=0 && 'Calendar'[Date] <= _Today+11 && 'Calendar'[Date]>_Today))
//Calculate the last day in the range: if there 2 weekend days ahead, add 9 days, otherwise 11 days.
//Please verify if this logic works if there are 3 weekend days ahead
VAR _LastDayInRange = IF(_WeekendDaysAhead = 2,_Today+9,_Today+11)
VAR _WeekendDaysPast = COUNTROWS(FILTER('Calendar','Calendar'[Weekday]=0 && 'Calendar'[Date] >= _Today-5 && 'Calendar'[Date]< _Today))
VAR _FirstDayInRange = IF(_WeekendDaysPast = 0, _Today-3,_Today-5)
RETURN IF('Calendar'[Date] >= _FirstDayInRange && 'Calendar'[Date] <= _LastDayInRange && 'Calendar'[Weekday] =1,1,0)
The resulting calendar table:
Hope this helps.
JJ
@DoubleJ How can I convert this to show me next 7 working days starting today?
Also Today considering it that it will be published in Power Bi service so should work according to te timezome (Austrlia, melbourne)
@learner03 , Not very clear. You can use work day rank to get this going
Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date],,ASC,Dense)
new measure =
var _max = maxx(allselected(Date),Date1[Workday Rank])
var _min =_max, -1 +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Workday Rank] >=_min && 'Date'[Workday Rank] <=_max))
Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...
@amitchandak To summarise my requirement-
I need to display 11 workdays in a table or matrix visual and those days are 3 day before today, today and 7 days after today.
I made the 3 columns that you said above but can't understand the measure as to how it will display these 11 days in visual
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |