How to create dynamic column headers & custom conditional formatting (match an easy Excel process)

[Izzii0x]

Hi all,

I am really eager to build a report I refresh regularly (now in Excel) into Power BI so I can fully automate it from data > powerpoint. 

Currently, the main hurdle is that I have simple tables in Excel that I don't know how to replicate, the problem has 2 main parts: the column headers and conditional formatting.

Column Headers

In Excel, there are always only 13 data columns for each table, the furthest right being the start of the current month (e.g., 01/10/2021), using a formula. The 12 columns then each have EDATE(column-to-right, -1) so that eventually I have a table that always contains the previous 13 rolling months as headers.

Is there any way I can do this in PBI? I am confident I can do the DAX/PQ needed to get the dates, but how do I ensure the column headers in the table (not the PQ table, but the visualisation of a table - as I will have different tables by product) relate to these dates?

Conditional Formatting

Additionally, each row of data within these tables will contain a KPI, and each row will individually have 'guardrails' of which if the KPI exceeds will show as red etc. I can add these guardrails in another table and create a relationship between them - but is there a way to have custom conditional formatting whereby the table cell will highlight a colour based on the value and on the value of its guardrail, but that could be different to the guardrail below it?

I've added a screenshot of what the table looks like in Excel, currently the PBI report does not exist because I am reluctant to start the work without knowing if I will ever be able to actually recreate it.

I appreciate any help you can offer!

[v-luwang-msft]

Hi @Izzii0x ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft · Accepted solution]

Hi @Izzii0x ，

The following article describes in detail about Power BI Conditional Formatting for Matrix and Table Visuals.Refer it and check if it could help you solved your question about Conditional Formatting.

refer:https://www.mssqltips.com/sqlservertip/6265/power-bi-conditional-formatting-for-matrix-and-table-vis... 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Izzii0x]

I've solved half of this via trial and error, if anyone stumbles across the dynamic column headers question and is wondering:

I brought several start of month dates into a test table (way more than I required), with dummy values assigned. Then I sorted the list in PQ by date ascending (sorting via Table.Buffer to ensure the sorting locks in). Then, I selected the matrix table and put the dates into columns and set the filter to the last 13 calendar months. 

Actually quite a simple fix to what I was expecting to be more of a complex problem!