To solve this objective, you would need to break down the data differently, and for each day with open record, you will have a dedicated record in your table. To do it 100% in QuickSight you would need to start with three table and merge them:
- A calendar table
- A color table
- You existing table
Now, using the join mechanism you can create a cartesian product of all dates and colors (To merge between Calendar and Colors you will need to create a “dummy” column with the same value for both Calendar and Colors. Perform left-join to your original table to as well. Next you can create a calculated field that returns true if the date and color of the cartesian product match the record in your table. Then, you can filter only the records with the true value and end up the necessary format.
I hope it was clear enough to try it.