Conditional Formatting for Date / Daterange

[phyten]

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 😄 

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

[v-yuezhe-msft · Accepted solution]

Hi @phyten,

We are not able to achieve your requirement using conditional formatting in Power BI Desktop.

Firstly, conditional formatting doesn’t work with Date type data, it works with numeric values.

Secondly, after creating a new column to calculate days between current date and your date column,  we are not able to specify data range in the following screenshot, we are only able to set fixed values for Minimum, Center and Maximum.


About these the above issues of conditional formatting in Power BI Desktop, I would recommend you vote them in the following ideas.
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14458686-table-conditional-format...
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16417966-conditional-formating-fo...

Thirdly, to work around this issue, you can create charts in SQL Server 2016 Reporting Services and add conational formatting for Date column in the charts , then pin the Reporting Services report item to Power BI Dashboard. For more information, please check the following articles.

Adding Conditional Formatting (Reporting Services)
Pin Reporting Services items to Power BI Dashboards



Thanks,
Lydia Zhang