Forum Discussion
Matrix Visual Showing Occupancy per day excluding Weekends and Public Holidays
Hi there,
I have a list for car rental showing the start date of the rental and the return date (Utilisation). What I am looking for is to represent the data in a calendar visual showing the days in which the car is utilised.
Currently, I have (and happy with but welcome suggestions) using the Matrix Visual with the Day name on the columns and week ending on the rows to represent a calendar. I have also figured out a DAX measure that counts the number of cars utilised on a certain day.
I need help with figuring out how I can exclude weekends, as well as public holidays from this visual (assume any day within the period as a public holiday). This is because the car rental does not count towards weekends and public holidays.
See attached the .pbix file I have created for reference, and data table if needed.
Thank you very much in advance.
Link to files: https://1drv.ms/u/s!Ag4OpcrFjtQuh-Y3dFTEMn3E1die5w?e=H5rssX
2 Replies
- amitchandak
Super User
WarrenBarrell , You need have flag in you table for week and holiday.
For week you can code like
work day = if(weekday([Date],2) <6, 1,0)
and you can filter data for work day =1
Consolidate week end holiday in one column and filter
Hi WarrenBarrell ,
The link to the document is no longer available.
You can refer to this blog to create a [working day] field for the calendar table.
Use DAX to create a calendar table with holidays
This way you can add something like with 'Calendar' [working day] = "working day" to the formula to filter.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
