DAX to compare two Dates and mark the column in Red and NA when the condition doesn't match

[deb_power123]

Hi All,

In my School table i have two columns :- 1) AttendanceLocalDate  2)ServerDate.

If AttendanceLocalDate column showing the attendance of students is equal to ServerDate of the attendance serverand the serverDate is equal to systemdate then only it should  populate the values of Attendance localtime, if AttendancelocalDate is not equal to ServerDate[which should also be equal to today's system date], then it should show "NA" with NA text in red color.

My Source table :

AttendancelocalDate	Location	StudentID	Name	ServerDate
20.07.2021 11:05AM	Gate1	101	Tom	20.07.2021 11:05AM
20.07.2021 10.03.22	Gate1	102	Hary	20.07.2021 10.03.22
20.07.2021 10.09.21	Gate2	103	Rohan	20.07.2021 10.09.21
20.07.2021 09.03.18	Gate2	105	Mohan	20.07.2021 09.03.18
19.07.2021 10.08.22	Gate2	109	Sam	19.07.2021 10.08.22
19.07.2021 10.03.12	Gate2	110	Sally	20.07.2021 10.03.12

My expected table matrix:

It should populate Attendance total column as NA with red text where the AttendancelocalDate is not equal to serverDate[which should be today's system date only so we need to check this condition as well].Server date also has the potential to show olddates so we need to check it is today's date as the second condition after checking the first condition of AttendancelocalDate equal to Serverdate

AttendancelocalDate	Location	StudentID	Name	ServerDate
20.07.2021 11:05AM	Gate1	101	Tom	20.07.2021 11:05AM
20.07.2021 10.03.22	Gate1	102	Hary	20.07.2021 10.03.22
20.07.2021 10.09.21	Gate2	103	Rohan	20.07.2021 10.09.21
20.07.2021 09.03.18	Gate2	105	Mohan	20.07.2021 09.03.18
NA	Gate2	109	Sam	19.07.2021 10.08.22
NA	Gate2	110	Sally	20.07.2021 10.03.12

Regards

Sameer

[Greg_Deckler]

@deb_power123 Try creating two columns. Use the numeric column for your conditional formatting.

Column = 
    SWITCH(TRUE(),
        INT(TODAY()) <> INT([ServerDate]),0,
        [AttendancelocalDate] <> [ServerDate],0,
        1
    )

Column 2 = 
    SWITCH(TRUE(),
        INT(TODAY()) <> INT([ServerDate]),"NA",
        [AttendancelocalDate] <> [ServerDate],"NA",
        [AttendancelocalDate] & ""
    )