Forum Discussion

user_34's avatar
user_34
Frequent Visitor
2 years ago
Solved

Creating a table which has dynamic header and data

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 

CategoryFileStart TimeTarget TimeReceived TimeFinal timeDate
As32983:00 AM8:00 AM3:00 AMMet08-04-2024
As32993:00 AM8:00 AM4:00 AMMet07-04-2024
As33003:00 AM8:00 AM5:00 AMMet06-04-2024
As33013:00 AM8:00 AM6:00 AMMet08-04-2024
As33023:00 AM8:00 AM7:00 AMMet07-04-2024
As33033:00 AM8:00 AM8:00 AMMet06-04-2024
As33043:00 AM8:00 AM9:00 AMNot Met08-04-2024
Bs33054:00 AM8:00 AM10:00 AMNot Met07-04-2024
Bs33065:00 AM8:00 AM11:00 AMNot Met06-04-2024
Bs33076:00 AM8:00 AM12:00 PMNot Met08-04-2024
Bs33087:00 AM8:00 AM1:00 PMNot Met07-04-2024
Bs33098:00 AM8:00 AM2:00 PMNot Met06-04-2024
Bs33109:00 AM9: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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies