Forum Discussion
Matrix style fiscal year grid
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?
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.
4 Replies
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.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.
