Bedingte Formatierung für Spalten / Conditional formatting for columns

[nevs77]

Hallo liebe community,

Ich habe ein Problem an dem ich schon über eine Woche sitze und ich einfach nicht weiter komme. Vielleicht habt ihr eine Idee wie ich es umsetzten könnte.

Ich habe einen Matrix wo die Spalten die einzelnen Monate der GuV darstellen. Also Januar bis Dezember. Die Daten sind immer eine Mischung aus Ist und Forecast zahlen. Bsp.: Jan - Sep sind es ist Zahlen und die Spalten ab Okt sind Forecast zahlen. Im nächsten Monat werden dann Jan - Okt die ist Zahlen sein und ab Nov sind es die neuen Forecast zahlen.

Nun versuche ich die Spalten der Forecast Monate farblich zu markieren, damit man diese sofort erkennbar sind. Ich bräuchte also eine Bedingte Formatierung auf Basis des Spaltenüberschriftes / Monatsname/ Datum.

Alle benötigten Informationen wie Monat oder Datum sind im Bericht, im Power Query oder im Datenmodel vorhanden. Nur fehlt mir eine Möglichkeit die Spalten nicht anhand der Werte sondern des Datums ein zu färben.

Kann mir jemand hier einer Idee aushelfen?

Hello dear community,

I have a problem that I've been sitting for over a week and I just can't get ahead. Maybe you have an idea how I could implement it.

I have a matrix where the columns represent the individual months of the income statement. So January to December. The data are always a mixture of actual and forecast figures. For example: Jan - Sep are actual values and the columns from Oct on are forecast figures. In the next month Jan - Oct will be the actual values and from Nov it will be the new forecast figures.

Now I try to highlight the columns of the forecast months in color, so that they are immediately recognizable. So I would need a Conditional Formatting based on the column header / month name/ date.

All the needed information like month or date is available in the report, power query or data model. Only I am missing a way to color the columns based on the date rather than the values.

Can someone help me out with an idea here?

Vielen Dank / thanks

Sven

[nevs77]

I also tried on me to refer to another column me than the cell value, but there it always counts the dates.

[nevs77]

@amitchandak Thank you for your answer.
But exactly here is my problem. I must not take the values for the formatting, but the date which is also the column header.

As an example I have created an Excel file. Here the screenshots of it. In Excel, I simply refer to the formatting whether the month is before or after a date. If it is after the date, the column should be colored. For the formatting i used follow form here: =if(C$4>=$C$4;TRUE;FALSE) and by the Condition i say only if is it TRUE fill the columns gray. 

I also tried to color the cell according to another value but I can't make a comparison with dates.

Maybe I just did not understand your answer.

[amitchandak]

@nevs77 , Conditional formatting can be used based on values, as far as I understand that

You can use color measure using field value option

Color = Switch( True() ,
Max(Table[Status]) < 200 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Max(Table[Status]) < 500 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
)
)

I have something similar here

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI

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/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[nevs77]

Hello @amitchandak ,

I have integrated your solution in a slightly different way. Since the colors are given, I have already specified the color codes in hex in Power Query. So I can also color the cells.

Unfortunately it is also so, that with some P&L positions no values for individual months are there and thus these are not colored.

Is there really no solution in Power BI for conditional formatting of whole columns? Are there any add-in visuals that allow it?

Thanks, Sven