Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
MartinHatch
New Member

How can I show a matrix by day/month, when source date values are in From > To structure

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

 

  • Name
  • Date From
  • Date To

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

  • Data is "From" and "To" instead of a fixed date
  • Data needs to be refreshed from a data source (in this case a SharePoint List)
  • We do not have data-points for every single day (only the "out of office" travel data) but we still need to RENDER every single day

Examples of the data

  1. Name: John Smith | From: 10/12/2018 | To: 12/12/2018
  2. Name: John Smith | From: 24/12/2018 | To: 28/12/2018
  3. ... etc ...

Example of how I rendered it in Excel

 

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@MartinHatch,

 

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@MartinHatch,

 

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

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.