Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
So I have some source data which documents "Staff Travel"
The format is fairly basic (this has been simplified for the purposes of this post):
And they want to be able to display this in a Matrix showing who is either "IN" or "OUT" of the office on each day of each month.
I managed to do this in Microsoft Excel fairly quickly using a combination of WEEKDAY and COUNTIFS formulas (it even automatically generates the full matrix each month, only shows Mon-Fri, auto-calculates the first week-day of the month and formats them with separators per week).
*However* .. the client has now asked us to build this natively in Power BI - partly for distribution but mainly so that the delivery, style and design of the reportd ties in with their other Power BI based stuff.
So .. my question to your good selves.
Is this actually possible? (without a tonne of work)
Summary
Challenges
Examples of the data
Example of how I rendered it in Excel
Solved! Go to Solution.
Try to drag [Date] from a calendar table to Columns, measure below to Values and apply Conditional formatting.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN IF ( d >= SELECTEDVALUE ( Table1[From] ) && d <= SELECTEDVALUE ( Table1[To] ), "IN", "OUT" )
Try to drag [Date] from a calendar table to Columns, measure below to Values and apply Conditional formatting.
Measure = VAR d = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN IF ( d >= SELECTEDVALUE ( Table1[From] ) && d <= SELECTEDVALUE ( Table1[To] ), "IN", "OUT" )
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
8 | |
7 | |
2 | |
2 | |
2 |
User | Count |
---|---|
6 | |
5 | |
4 | |
4 | |
4 |