Conditionally Format Time Values in a Table

[mjholland]

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

[MattAllington · 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. 

[mjholland]

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

[MattAllington]

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

[mjholland]

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