Matrix style fiscal year grid

[DJ_Jamba]

Hello Community!
My left eyebrow is flickering a lot over this 😂

I know there must be several ways to achieve this but I'm looking for the most optimal way 😁

I have:

 - A calendar table

 - A progress reporting table (SharePoint List)

 - A project reporting table (SharePoint List)

Relationships:

In the projects table there is only a Due By date column and a Period (text type e.g. 2023-2024). No other date field.
Both progress and project tables have a "RAG Status" column.

In the first 3 months of the year (fiscal) there's hardly any progress reported for months April - June for any of the projects. However, I still want to be able to show that there's no progress using a grey block in e.g. a grid/matrix. If there is progress for any of the months then I just want to colour a block according to what the RAG status is (Red, Amber or Green).

So, pretend we are looking at this in June, a grey block means no progress was submitted for that month and the coloured blocks have been pulled from the RAG Status column, how would I achieve the below?

[Anonymous · Accepted solution]

Hi @DJ_Jamba ,

Have you solved your problem?
If not, you can try this way, here is my sample data ( Note: Power BI can't identify Amber, so I changed it into Blue 😞

You can use this DAX to create a measure:

Measure = 
IF(
    MAX('Progress Reporting'[RAG Status])="",
    "grey",
    MAX('Progress Reporting'[RAG Status])
)

Follow the diagram below to set up:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DJ_Jamba]

Works well. Thanks very much for your help!

[DJ_Jamba]

Hi Dino

Thanks for the suggestion.
I'm away on business but will give it a try towards the end of the week

[lbendlin]

COALESCE should be able to help you. But in general to report on things that are not there you need to use disconnected tables and cross joins.