Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
inglexjc
Helper IV
Helper IV

Conditional Formatting Past Due Date

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 NameDate_Due
Smith, j10/06/2020
x, Greg11/07/2022
Harris06/14/2023
  
  
  
  

 

1 ACCEPTED SOLUTION

It should be :

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

tells us if it works

View solution in original post

10 REPLIES 10
AilleryO
Memorable Member
Memorable Member

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)

@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:

inglexjc_0-1683213656572.png

 

It should be :

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

tells us if it works

@AilleryO

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

inglexjc_0-1683214259356.png

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

inglexjc_1-1683214298574.png

 

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

@AilleryO 

 

That was it!  Thank you.

vicky_
Super User
Super User

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.

@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?

inglexjc_0-1683209339096.png

 

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

@AilleryO 

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

inglexjc_0-1683211081232.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.