Creating a table which has dynamic header and data

[user_34]

Hi all 
I am creating a new table in which I am creating a date picker using this DAX
Calendar =
VAR Days = CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) )
RETURN ADDCOLUMNS (
Days,
"Formatted Date", FORMAT([Date], "yyyy-mm-dd"),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,
"Year Month", FORMAT ( [Date], "mmm yy" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] ),
"Is Working Day", NOT WEEKDAY([Date]) IN {1,7}
)
Now I have data 

Category	File	Start Time	Target Time	Received Time	Final time	Date
A	s3298	3:00 AM	8:00 AM	3:00 AM	Met	08-04-2024
A	s3299	3:00 AM	8:00 AM	4:00 AM	Met	07-04-2024
A	s3300	3:00 AM	8:00 AM	5:00 AM	Met	06-04-2024
A	s3301	3:00 AM	8:00 AM	6:00 AM	Met	08-04-2024
A	s3302	3:00 AM	8:00 AM	7:00 AM	Met	07-04-2024
A	s3303	3:00 AM	8:00 AM	8:00 AM	Met	06-04-2024
A	s3304	3:00 AM	8:00 AM	9:00 AM	Not Met	08-04-2024
B	s3305	4:00 AM	8:00 AM	10:00 AM	Not Met	07-04-2024
B	s3306	5:00 AM	8:00 AM	11:00 AM	Not Met	06-04-2024
B	s3307	6:00 AM	8:00 AM	12:00 PM	Not Met	08-04-2024
B	s3308	7:00 AM	8:00 AM	1:00 PM	Not Met	07-04-2024
B	s3309	8:00 AM	8:00 AM	2:00 PM	Not Met	06-04-2024
B	s3310	9:00 AM	9:00 AM			08-04-2024

Now I want create a table in which category name and today date,yesterday date and day before yesterday date as a header  in which target should show as values where I want to apply conditional formatting like

1. for 8 April for the category a if their final time is between target time and start time it should show green 

2. if final time =null but system time > Target time  show red
3. if final time =null but system time < Target time  show yellow
Attaching the excel file 
https://docs.google.com/spreadsheets/d/1DQd8Xj1mJzYnZeTWXmgNRJIOwCLaz10lBBKsDQK91q4/edit?usp=sharing

[v-yangliu-msft · Accepted solution]

Hi  @user_34 ,

Thanks for the reply from @amitchandak , please allow me to provide another insight: 

Here are the steps you can follow：

1. Create measure.

color =
var _today=TODAY()
return
SWITCH(
    TRUE(),
    MAX('Table'[Date])=_today&&MAX('Table'[Final time])>=MAX('Table'[Start Time])&&MAX('Table'[Final time])<=MAX('Table'[Target Time]),"green",
    MAX('Table'[Date])=_today&&MAX('Table'[Final time])=BLANK()&&MAX('Table'[Start Time])>MAX('Table'[Target Time]),"red",
    MAX('Table'[Date])=_today&&MAX('Table'[Final time])=BLANK()&&MAX('Table'[Start Time])<MAX('Table'[Target Time]),"yellow")

2. Select filed – Conditional formatting – Background color.

3. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@user_34 , You can create a measure and use that in conditional formatting using field value option

example measures

Color = Switch( True() ,
Max(Table[Status]) < 200 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Max(Table[Status]) < 500 ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
) ,
Switch(true(),
[ERB] = .25 , "Orange" ,
"Red "
)
)

Color Date =
var _min =minx(allselected(Date,Date[Year])
return
Switch( true(),
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen",
FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue",
"red")

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...