Attendance and Leave Balance

Hi All,

I am relitavtily new to PowerBI and stuck with an problem from last one week. I would really appretiate if the community can guide me in the right direction.

We are trying to build a system which can give us a report as to how many leaves and the type of leave to dededuct bases the total hours worked by an employee from their leave balance. 

We have two table 

Table 1 - 

Employee ID(Unique ID)

Employee Name

Date

Login hours (decimal)

Note - Each employee has multiple line items for each work day of the month.

Table 2

Employee ID(Unique ID)

Employee Name

Casual Leave Balance(Number)

Earned Leave Balance (Number)

Note these are the type of leaves allocated to employees each month. Each employee has only one line item 

Expected Out comes

Divide each day into two parts Session 1 and Session 2

If the total hours is above 8 and its weekday (Mon - Fri)

If the total hour is between 4 - 8 its a half day leave  Session 2 is a leave.

If the total hours is less than 4 hours is a full day leave  - Session 1 and 2 both are leave

Each session leave = 0.5 leave (either Casual leave or earned Leave)

Now for each session leave depending on the leave balance of the employee allocate leave while taking into account that total leaves applied should not exceed the leave balance. 

Also, first exhast casual leave balance then Earned Leave balance and if both exhasted marked is Loss of Pay.(note the first two are paid leaves)

Note - The paid leave balance must be exhasted first, for example if an employee takes leave, and has 0.8 Casual sick leave balance, first half should be marked as Casual sick leave and second half should be marked earned leave (if above 0.5) else Loss of Pay.

The leaves allocation should be in format (This is excel as we are using excel macro currently)

all the above will work on month to month bases.

thanks in advance.

dax