DAX to compare with system date and find attendance dates for today

[deb_power123]

Hi All,

I have a table named school  as shown below . Here Attendance dates columns are populated on a daily basis.It can also contain old records sometimes but we need to mark them as NA and red color in the cell of the tabular matrix in powerBI

As per my requirement if the attendance date is not equal to the systemdate then it should show NA and mark the cell as red dynamically. Say today is "15.07.2021" then the third row has entry "14.07.2021 10.09.21" this cell needs to be filled with red color with text NA replacing "14.07.2021 10.09.21".

Could anyone please suggest any DAX to handle this situation?

Attendance  Date	swipeLocation	StudentID	Name
15.07.2021 11:05AM	Gate1	101	Tom
15.07.2021 10.03.22	Gate1	102	Hary
14.07.2021 10.09.21	Gate2	103	Rohan
15.07.2021 09.03.18	Gate2	105	Mohan
15.07.2021 10.08.22	Gate2	109	Sam
15.07.2021 10.03.12	Gate2	110	Sally

Kind regards

Sameer

[Samarth_18]

Hi @deb_power123 

You can create two measure one for checking dates and another for conditional formating.

- Create a measure with below code for checking date with today

check_date = IF(MAX(Attendence_Table[ Attendance  Date]) < NOW(),"NA",FORMAT(MAX(Attendence_Table[ Attendance  Date]),"mm/dd/yyyy hh:mm:ss"))

- Create a measure with below code and use it conditional formating

Format_Measure = IF([check_date] = "NA","red")

 PFB screenshot

Thanks

[deb_power123]

Thankyou @Samarth_18  for the above DAX. I am really sorry , i missed an important point.In my table i have two columns :- 1) AttendanceLocalDate  2)ServerDate

If AttendanceLocalDate column is equal to ServerDate then only it should fetch populate the values of Attendance localtime, if it is not equal to ServerDate [server date should be again equal to system date always]it should show as per the below expected  in table matrix

My Source :

Attendancelocal Date	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[server date should be always today's system date , this check should also be done] so we have 2 checks here.Today's system date is 20.07.2021

Attendancelocal Date	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

[Samarth_18 · Accepted solution]

Hi @deb_power123

In that condition you can update measure with below code:-

check_dates = IF(MAX(Attendance[Attendancelocal Date]) = MAX(Attendance[ServerDate]),FORMAT(MAX(Attendance[Attendancelocal Date]),"m/dd/yyyy hh:mm:ss AM/PM"),"NA")

Add below measure in conditional formating under "Font Color" 

Formating_Measure = IF([check_dates] = "NA","red")

You will get below output:-

Thanks