Forum Discussion
Date Conditional Formatting
Hello!
I have a data table with UK post code areas and a date of the last sale in that area, for example:
| Post Code Area | Last Sale |
| AB | [NULL] |
| AL | 04/04/2023 |
| B | 23/02/2023 |
| BA | [NULL] |
| BB | 11/11/2022 |
| BD | 27/03/2023 |
| BH | 01/02/2023 |
| BL | 28/01/2023 |
| BN | [NULL] |
| BR | 01/04/2023 |
What I'd like to do is plot these post codes on a map but colour code them.
So, on the basis today is 05/04/2023 I'd like to colour code the plots as such:
1. If Last Sale is Null/Blank then BLUE
2. If Last Sale is less than a month ago then GREEN
3. If Last Sale is between a month and three months ago then YELLOW
4. If Last Sale is greater than three months ago then RED
Is this possible? I was presuming I might need to create four separate measures?
Thanks for any help you could provide.
RDF
3 Replies
RDF25087 , Create a measure like
M1= count(Table[Last Sale]) +0
Then create a color measure and use that in conditional formatting using field value option
Color =
var _diff= datediff(max([Last Sales]), Today(), month)
Switch(true(),
[M1]= 0 , "Blue" ,
_diff <=1, "Green",
_diff <=3, "Yellow",
"Red"
)
How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3amitchandak - thank you for the response.
Apologies for being a bit daft but how do I reference the M1 measure in the color measure? It's telling me [M1] is an unexpected expression.
Thanks
RDF
