Conditional format based on coverage date

[bodnaran]

Hello guys,

I am struggling for a while to reproduce an excel type matrix in Power BI.

The input from the users is not comming every day, it is made once a week.

I have a date table which is covering the whole year (01.01.2023 -31.12.2023). In this table I have the following columns:

My facts table is like below:

Coverage	Day of Year for Coverage	Day of Year for Today	Project_name	Customer_name	Plant_name	Int_ext	Update_date	Week of Year	Plant ID	Customer ID	Project ID
29-09-2023	272	249	1	11	123	Ext	05-09-2023	36	2	5	12
08-09-2023	251	249	2	22	234	Ext	05-09-2023	36	2	5	11
18-09-2023	261	249	3	33	345	Ext	04-09-2023	36	4	7	15
26-09-2023	269	249	4	44	456	Int	04-09-2023	36	1	2	2
26-09-2023	269	249	5	55	567	Int	04-09-2023	36	1	2	7
26-09-2023	269	249	6	66	678	Ext	04-09-2023	36	1	3	9
08-09-2023	251	249	7	77	789	Int	04-09-2023	36	1	2	4
08-09-2023	251	249	8	88	900	Int	04-09-2023	36	1	2	5
24-10-2023	297	249	9	99	1011	Int	04-09-2023	36	1	2	6
07-11-2023	311	249	10	110	1122	Int	04-09-2023	36	1	2	8
27-11-2023	331	249	11	121	1233	Int	04-09-2023	36	1	4	9
29-09-2023	272	249	12	132	1344	Ext	04-09-2023	36	1	3	10
07-09-2023	250	249	13	143	1455	Ext	04-09-2023	36	3	5	13
18-09-2023	261	249	14	154	1566	Ext	04-09-2023	36	4	6	14
28-11-2023	332	249	15	165	1677	Int	04-09-2023	36	1	1	1
27-10-2023	300	249	16	176	1788	Int	04-09-2023	36	1	4	5
11-09-2023	254	249	17	187	1899	Ext	04-09-2023	36	4	8	16
02-10-2023	275	249	18	198	2010	Int	28-08-2023	35	1	2	3

In the end I need a matrix which needs to look like the "excel" example above. I tried to use some function that shows me the "green" until the coverage day but I get red only on the day of coverage like below:

DAX codes I tried

IsAllSmaller = 
IF (
    COUNTROWS (
        FILTER (
            F_CustomerUpdate,
            F_CustomerUpdate[Day of Year for Coverage]
                >= MAX ( F_CustomerUpdate[Day of Year])
        )
    )=0,
    "Green",
    "Red"
)

Test = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(F_CustomerUpdate[Day of Year for Coverage])=BLANK(),0,
    SELECTEDVALUE(F_CustomerUpdate[Day of Year for Coverage]) > SELECTEDVALUE(F_CustomerUpdate[Day of Year]), 1 , 
    SELECTEDVALUE(F_CustomerUpdate[Day of Year for Coverage]) )

Any idea how can I show Green until the coverage day and red after the coverage day?

[bnjmnnl]

Wouldn't the following work?:

IF(
date <= coverage, "green", "red"
)

This would make all dates before and on coverage date show green and everything after red.