cancel
Showing results for 
Search instead for 
Did you mean: 
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 a 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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

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 a 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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors