Comparing Hours Scheduled to Hours Worked in 2 Fact Tables

[ronrule]

pbix in OneDrive: https://1drv.ms/u/s!Aj43wr7aCxS_1jxQtptl6nx5r_re?e=ThSWml

• I have a Schedule Fact table that contains Date, EmployeeID, StartTimes and EndTimes.
• I have a Timesheet Fact table that contains Date, EmployeeID, InTimes and OutTimes.
• They share an Employee Dimension and Date Dimension, joined by EmployeeID and Date.

I would like to show if the hours were worked as scheduled. Also if they were late, absent, or on-time. 

I'm struggling understanding how to model or create measures that compare without running into circular references.

[ryan_mayu]

the file is restricted due to the sensitivity label

[ronrule]

Thanks! Updated it to Public.

[ryan_mayu]

What's the expected output based on the sample data?

[ronrule]

I would like to show if the hours were worked as scheduled. Also if they were late, absent, or on-time. 

[ryan_mayu]

@ronrule 

you can try something like below

Measure = 
VAR _start=max(Schedule[Start Time])
VAr _end=max(Schedule[End Time])
VAR _scanstart=max(Timesheet[ScanInTime])
VAR _scanend=max(Timesheet[ScanOutTime])
return if(ISBLANK(_start),blank(),if(_scanstart>_start,"late",if(ISBLANK(_scanstart),"absence","ontime")))