DAX to return non-existing records for the specific date filter

[DivakarKrishna]

Dear Team,

I have a requirement to display the records where they don't have punch-in for the specific day.

I wrote the below DAX only for today,  and it's working perfectly.

DAX for Today only :-

Check Employee =

IF (

COUNTROWS(

EXCEPT(

VALUES(PBI_WFC_Emp_Master[employeeId]),

CALCULATETABLE(

VALUES(PBI_WFC_Time_Attendance[employeeId]),

USERELATIONSHIP(PBI_WFC_Emp_Master[employeeId],PBI_WFC_Time_Attendance[employeeId]),

PBI_WFC_Time_Attendance[Punch In Date]=TODAY()

)

)

)>0,1,0)

Instead of Today, I want the date to be dynamic by whichever date was chosen from the date slicer, So I wrote the below DAX but it's not working properly, it's returning all records which is incorrect.

DAX for selected date from slicer:

Check Employee =

VAR _date = ALLSELECTED('Calendar'[Date])

RETURN

IF (

COUNTROWS(

EXCEPT(

VALUES(PBI_WFC_Emp_Master[employeeId]),

CALCULATETABLE(

VALUES(PBI_WFC_Time_Attendance[employeeId]),

USERELATIONSHIP(PBI_WFC_Emp_Master[employeeId],PBI_WFC_Time_Attendance[employeeId]),

PBI_WFC_Time_Attendance[Punch In Date]=  _date

)

)

)>0,1,0)

Can you please suggest me on how to fix this above DAX? I want the non-existing records selected by the specific date (01/25 or 01/26 etc... )

Thanks,

P.K.Divakar

[amitchandak]

@DivakarKrishna , Try like

VAR _date = Maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])

and rest is same

[DivakarKrishna]

Hi Amit bro @amitchandak ,

Tried this one already, but not working.. When I select date in date slicer, table returns blank value.

Check Employee =

VAR _date = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])

RETURN

IF (

COUNTROWS(

EXCEPT(

VALUES(PBI_WFC_Emp_Master[employeeId]),

CALCULATETABLE(

VALUES(PBI_WFC_Time_Attendance[employeeId]),

USERELATIONSHIP(PBI_WFC_Emp_Master[employeeId],PBI_WFC_Time_Attendance[employeeId]),

PBI_WFC_Time_Attendance[Punch In Date]= _date

)

)

)>0,1,0)

[amitchandak]

@DivakarKrishna , Try like

Check Employee =
VAR _date = MAXX(ALLSELECTED('Calendar'),'Calendar'[Date])
RETURN
IF (
COUNTROWS(
EXCEPT(
VALUES(PBI_WFC_Emp_Master[employeeId]),
summarize(filter(CALCULATETABLE(PBI_WFC_Time_Attendance,
USERELATIONSHIP(PBI_WFC_Emp_Master[employeeId],PBI_WFC_Time_Attendance[employeeId]) )
PBI_WFC_Time_Attendance[Punch In Date]= _date),PBI_WFC_Time_Attendance[employeeId])
)
)
>0,1,0)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[DivakarKrishna]

Hi @amitchandak ,

Can you please share your email id, so i can share sample .pbix file?

Thanks!