Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Sick Leave Balance

    Posted Aug 12, 2022 11:32 AM
    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
    ------------------------------

    Attachment(s)

    pbix
    TimeOff.pbix   92 KB 1 version
    xlsx
    TimeOff.xlsx   10 KB 1 version


  • 2.  RE: Sick Leave Balance
    Best Answer

    Posted Aug 16, 2022 08:33 PM
      |   view attached


    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 = 4
    VAR 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 = 6
    VAR 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
    ------------------------------

    Attachment(s)

    pbix
    TimeOff_new.pbix   110 KB 1 version


  • 3.  RE: Sick Leave Balance

    Posted Aug 18, 2022 10:43 AM
    Kaz,

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

    Edgar Martinez

    ------------------------------
    Edgar Martinez
    CPA, CISA
    Martinez & Morales
    Duluth GA
    4049161228
    ------------------------------



  • 4.  RE: Sick Leave Balance

    Posted Aug 18, 2022 07:42 PM
    @Edgar Martinez,  I'm glad that solution worked for you.  It was a fun puzzle to try and solve - much more difficult that it would appear on the surface.  And, I'm sure there is a better solution out there, but if this gets you what you need, great!

    Kaz.​

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------