Issues with date for Absent Employee

[Anonymous]

Hi folks,

I am having issue where I need to mark the employee as "Absent" if there is no entry in database for the employee for particular date. e.g.  Emp 1 is not available on 11 April so it should map to Absent (Each Fri & Sat are holidays so we can ignore 9,10 and 16,17) PFA.

First check in entry is coming from database & I have created calendar table to get continuous dates but it is not showing the expected results. I want output as countinuos dates as attached (similar to Left outer join ) 
Help is much appreciated.

Power BI

Expected Output

[ERD]

Hello @Anonymous ,

From what I've seen/understood in your screenshots you can try the next actions:

1. Create a date column in your fact table in Power Query:
Click on 'First check in' date > Add column > Date > Day > Date only. Create relationship between this column and Date column in the Calendar table.
or

#"Inserted Date" = Table.AddColumn(#"PreviousStep", "First check in (date)", each DateTime.Date([First check in]), type date)

2. Create a measure in DAX:

Employee status = 
VAR currentDate = SELECTEDVALUE('Calendar'[Date])
VAR checkDate = COALESCE(MAXX(FILTER(T, T[First check in (date)] = currentDate), T[First check in (date)]),0)
RETURN
SWITCH(TRUE,
    WEEKDAY(currentDate,1) IN {6,7}, "Holiday",
    checkDate = 0, "Absent",
    "Present"
)

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

[Anonymous]

Thanks ERD,
It works as expected in the list.
But when I am trying to use Present, absent in slicer (making calc column) it doesnt work.
Also not able to calculate total absent days for selected employee e.g. in March 21 - 9 absent etc 

Appreciate your thoughts on that.