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
learner03
Post Partisan
Post Partisan

display weekdays in row in matrix visual

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.

akapoor03_0-1646806083651.png

 

1 ACCEPTED SOLUTION
DoubleJ
Solution Supplier
Solution Supplier

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:

DoubleJ_0-1646899294780.png

 


Hope this helps.

JJ

 

 

View solution in original post

4 REPLIES 4
DoubleJ
Solution Supplier
Solution Supplier

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:

DoubleJ_0-1646899294780.png

 


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)

amitchandak
Super User
Super User

@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

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.