Your solution worked for me, you are my HERO!. Thank you so much for your time and dedication to resolve this problem.

Original Message:

Sent: Aug 16, 2022 08:32 PM

From: Kaz Shakir

Subject: Sick Leave Balance

Edgar,

This is not a very elegant solution, more brute-force, but it gets the job done. See if this might work for you.

First I broke your 'Payrolls' table into two tables: 'Payrolls-sick' and 'Payrolls-worked', so that I could keep the sick hours and worked hours separate.

Next, I created a couple of calculated columns in the 'Payrolls-worked' table. The first is to calculate the hours worked for each month:

`Hours Worked MTD = VAR currentDate = 'Payrolls-worked'[Date]VAR currentEmployee = 'Payrolls-worked'[Employee]RETURN CALCULATE( SUM('Payrolls-worked'[Hours Worked]), ALL('Payrolls-worked'), 'Calendar'[Date] <= currentDate, 'Calendar'[Monthnumber] = FORMAT(currentDate, "MM"), 'Payrolls-worked'[Employee] = currentEmployee)`

And a second calculated column to determine the sick hours earned for the period (not accrued):

`Sick Earned = IF('Payrolls-worked'[Hours Worked MTD] > 115, 8, 0 )`

Then (this is where it gets messy), I created separate measures for each month to calculate the sick hours balance. And for April, the calculation is different, because it relies on the BegBalance table. So, the April measure looks like this:

`AprilBalance = VAR selectedMonthNum = 4VAR currentEmployee = MAX(Employees[Employee])VAR currentDate = MAX('Calendar'[Date])VAR begBal = COALESCE( CALCULATE( SUM(BegBalance[Sick Beg Balance]), ALL(BegBalance), BegBalance[Employee] = currentEmployee, BegBalance[Date] <= currentDate ), 0 )VAR sickEarned = CALCULATE( SUM('Payrolls-worked'[Sick Earned]), ALL('Payrolls-worked'), 'Payrolls-worked'[Employee] = currentEmployee, MONTH('Payrolls-worked'[Date]) = selectedMonthNum )VAR sickUsed = CALCULATE( SUM('Payrolls-sick'[Sick Used]), ALL('Payrolls-sick'), 'Payrolls-sick'[Employee] = currentEmployee, MONTH('Payrolls-sick'[Date]) = selectedMonthNum )RETURN MAX(MIN(begBal + sickEarned - sickUsed, 120), 0)`

Every other month just changes the internal variable called "selectedMonthNum", and the "begBal" variable is set to the measure that calculated the previous months balance. So, here is what the June measure looks like:

`JuneBalance = VAR selectedMonthNum = 6VAR currentEmployee = MAX(Employees[Employee])VAR currentDate = MAX('Calendar'[Date])VAR begBal = COALESCE( [MayBalance], 0 )VAR sickEarned = CALCULATE( SUM('Payrolls-worked'[Sick Earned]), ALL('Payrolls-worked'), 'Payrolls-worked'[Employee] = currentEmployee, MONTH('Payrolls-worked'[Date]) = selectedMonthNum )VAR sickUsed = CALCULATE( SUM('Payrolls-sick'[Sick Used]), ALL('Payrolls-sick'), 'Payrolls-sick'[Employee] = currentEmployee, MONTH('Payrolls-sick'[Date]) = selectedMonthNum )RETURN MAX(MIN(begBal + sickEarned - sickUsed, 120), 0)`

After I created all of the months from April to December, then I created a final measure, called Sick Balance, to bring them all together:

`Sick Balance = VAR currentDate = MAX('Calendar'[Date])RETURN SWITCH( MONTH(currentDate), 4, [AprilBalance], 5, [MayBalance], 6, [JuneBalance], 7, [JulyBalance], 8, [AugustBalance], 9, [SeptemberBalance], 10, [OctoberBalance], 11, [NovemberBalance], 12, [DecemberBalance], BLANK())`

Then the visualization is simply a table that includes the following columns: 'Employees'[Employee], 'Calendar'[YearMonthNumber], 'Payrolls-worked'[Hours Worked], 'Payrolls-worked'[Sick Earned], 'Payrolls-sick'[Sick Used], and [Sick Balance]:

Hope that helps.

Kaz Shakir.

------------------------------

Kaz Shakir

Sr. Program Manager, Asset Planning

TN

Original Message:

Sent: Aug 12, 2022 11:32 AM

From: Edgar Martinez

Subject: Sick Leave Balance

Hi Friends,

I need to find a measure that calculate the Hrs Accrued per month of the employees. The Hrs Accrued is calculated base on the Sick balance and Bi show a circular dependency error.

This is what must be accomplish:

This is what I have in Power BI:

Any help will be greatly appreciated.

Edgar Martinez

------------------------------

Edgar Martinez

CPA, CISA

Martinez & Morales

Duluth GA

4049161228

------------------------------