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
mjholland
Helper II
Helper II

Conditionally Format Time Values in a Table

Hi,

 

I have a table with start and finish times for a number of staff members and I'd like to be able to use conditional formatting to highlight any members who start after the allocated start time each day. Can someone tell me how to do this in Power BI without converting the time to a decimal number?

 

Thanks,

mjholland

1 ACCEPTED SOLUTION

 I don't recall if there are specifics conditional formatting coding based on formulas - I don't think so - yet.  I guess you could create a measure that returned the number of minutes late starting, and put formatting on that.  graduated formatting over values in a column is there already (in a table from memory), so it may do what you want. 

 

I I don't see any issue with the decimal number bit. You should be able to take the planned start and subtract the actual start - this will return a negative date/time. Format it as minutes.  I am pretty sure that will work. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

4 REPLIES 4

 I don't recall if there are specifics conditional formatting coding based on formulas - I don't think so - yet.  I guess you could create a measure that returned the number of minutes late starting, and put formatting on that.  graduated formatting over values in a column is there already (in a table from memory), so it may do what you want. 

 

I I don't see any issue with the decimal number bit. You should be able to take the planned start and subtract the actual start - this will return a negative date/time. Format it as minutes.  I am pretty sure that will work. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt,

 

Are you able to give me some detail on how you would create this measure to record the number of minutes late starting?

 

Thanks,

mjholland

it really depends what your data looks like and what you are trying to do. If the data set is small say less a 100,000 rows of data in a table, I think it may be easier just to do a calculated column. The column would compress pretty well. So assuming you have 2 columns of type date/time, you can write a calc column as 

 

time diff = datatable[planned start] - datatable[actual start]

 

if you format as time, it should show hh:mm difference. If you format as decimal, it should show fraction of a day difference, which you can en convert to decimal hours by multiplying by 24

 

you could write it as a measure, but it would be harder to write and understand, and I am not sure it would be worth the effort. (I am softening up in my old age). 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt,

 

I've figured it out. I'd already converted the time value to a decimal in a column (which I'd forgotten, I guess I'm softening too).

 

I've been working with average start and end times and thought it would all be a lot more complicated but I'd taken the measures I created to calculate these and subtracted them from values to get the difference.

 

Thanks,

mjholland

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.