Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Issues with date for Absent Employee

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 sho...
Need Help
ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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.