title: PowerBI - Conditionally format a date column relative to todays date
body: <p>I have a report, updated on a daily basis, that provides me with a list of remote devices connected to our server. </p>
  
  <p>A date column tells me the last date (and time) that a remote device sent data.
  I want to conditionally format the column so that, if the last date is:</p>
  
  <ul>
  <li>Today's date: the cell is green   </li>
  <li>Yesterday's date: the cell is yellow</li>
  <li>Up to 30 days old: the cell is orange</li>
  <li>older than 30 days: the cell is red</li>
  <li>empty: the cell is empty</li>
  </ul>
  
  <p>I've had a small degree of success in progressing towards my goal, but the colours are the wrong way round and I'm struggling with the "if" syntax. I can get today's date displaying yellow and every other date displaying green - but, to be honest, I've no idea quite how !!</p>
  
  <p><a href="https://i.stack.imgur.com/DXkCX.png" rel="nofollow noreferrer">Last data seen</a></p>
  
  <p>This is what I've done so far...</p>
  
  <p>I have created a measure "flag3" as below (with the intention of showing green for today, orange 1 day old and red for 2 days old)</p>
  
  <pre><code>flag3 = if(DATEDIFF(TODAY(),SELECTEDVALUE(DeviceStatus[Column1.lastDataRecordTime]),DAY),1,
      (if(DATEDIFF(TODAY()-1,SELECTEDVALUE(DeviceStatus[Column1.lastDataRecordTime]),DAY),2,
          (if(DATEDIFF(TODAY()-2,SELECTEDVALUE(DeviceStatus[Column1.lastDataRecordTime]),DAY),3)))))
  </code></pre>
  
  <p>And I have created conditional formatting based on background colour, 
  formatted by rules, 
  based on field flag3</p>
  
  <p>If the value is 1 - then green
  If it's 2 - then orange
  If it's 3 - then red </p>
  
  <p><a href="https://i.stack.imgur.com/QRmce.png" rel="nofollow noreferrer">Conditional Formatting based on Background Colour</a></p>
  
  <p>As I've said previously- it's sort of working but the colours are the wrong way round and I'm struggling with syntax.</p>