Conditional Formatting Past Due Date

[inglexjc]

I want to show font color (Red) if the Due Date is Today or before today.

Table name is TR_FSP_2 column is Date_Due.

Case Name	Date_Due
Smith, j	10/06/2020
x, Greg	11/07/2022
Harris	06/14/2023

[AilleryO]

If you're running it on your PC or on a server within the same time zone as yours, TODAY() is fine and so you don't need that part, just TODAY().

Otherwise replace by a value (ie 7 if you're 7hrs ahead of US and your report will be published on a US Server)

[inglexjc]

@AilleryO 

I am running on my computer for my time zone.  So if I take it out (timezoneOffset / 24).  Should I also take out todayInYourTimeZone?

I tried:

FormatColour =

var = TODAY ( )

return IF(Today >= [Date_due], "Red")  

And get sytax error:

[AilleryO · Accepted solution]

It should be :

FormatColour = IF(Today() >= [Date_due], "Red") 

tells us if it works

[inglexjc]

@AilleryO

I'm sure that would work.  But I'm still getting an error:

says my DATE_DUE doesn't exist.  Even though it does:

[AilleryO]

Your probem is that your're creating a measure and you should use a column for this formula. So it is not because your column doesn't exist, it's because a measure doesn't have a row context whereas the column will have a row context.

So if you create a new column (not measure) with the same formula it should work.

Let us know

[inglexjc]

@AilleryO 

That was it!  Thank you.

[vicky_]

There is a function called TODAY() that will return the curernt day (in UST time), which you can add (time zone offset / 24) to, depending on your timezone. 

So you can create a measure like so:
FormatColour =

var todayInYourTimeZone = TODAY + (timezoneOffset / 24)

return IF(todayInYourTimeZone >= [Date_due], "Red")

And use conditional formatting on your table to format the cells where the cell is overdue.

[inglexjc]

@vicky_ 

I am getting an error when creating this measure: "Failed to revolve name 'TODAY'. It is not a valid table, variable, or function name." Suggestions?

[AilleryO]

Hi,

TODAY is a function so needs parenthesis : TODAY()

To add to  @inglexjc answer, notice taht the TODAY() date is the one from the machine where the report is running, ie :

your PC when on Power BI Desktop

or the server on Power BI Service (and then it depends on the location of the server...).

You can check this location in Power BI admin.

Hope it helps

[inglexjc]

@AilleryO 

Okay I added the parenthesis but now get error about the 'timezoneoffset'.