display weekdays in row in matrix visual

[learner03]

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.

[DoubleJ · Accepted 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

[learner03]

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

[amitchandak]

@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...

[learner03]

@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