Forum Discussion

phyten's avatar
phyten
Helper I
9 years ago
Solved

Conditional Formatting for Date / Daterange

I want to compare the current date with the date stored in a column and f.e. color the background within a table accordingly.

Date already passed (in the past)-> red background

Date within 7 day range (future)-> background yellow

Date further away than 7 days -> background green

 

What i have tried until now:

Conditional formatting for dates: seems not to be possible (or i haven't found the option yet)

 

DAX DATEDIFF: datedifc = DATEDIFF(Now(),'test'[triagetest], DAY)   -> Gives me the error the the start date cannot be greater than the end date. Switching the two dates is also not an option since dates stored in triagetest can be in the future or in the past. If it would at least fill the cell with something like #Error I could detect it and format accordingly but this doesn't seem to be possible.

 

DAX FORMULA:  diff = 1.0*('test'[triagetest]-NOW()) -> This is what I use right now. Though in order to make conditional formatting for the column an option I have to f.e. summarize, average,... the values what I don't want to do. Also if I accept the fact that I summarize my values (which doesn't change the values within the cells at all so it would not change the outcome but adds something to the table), the only options for conditional formatting that I can find is gradient formatting though I want to change the color based on distinct ranges like mentioned above. 

EDIT: I can change it back to "Don't summarize" and the formatting itself stays. The option to change conditional formatting however vanishes :D 

 

I hope someone can help me with this. Maybe I am overlooking something completely basic.

 

 

1 Reply