Forum Discussion

Molin's avatar
Molin
Icon for Helper I rankHelper I
4 years ago
Solved

Calculating UK National Insurance rates

Dear all, 

I am working on a costing overview but are challenged by adding UK National Insurance (NI) on top of the base salary for our employees. Employees are categories into different National Insurance Category Letters following the below rate structures on a weekly basis.  

My data is structured in daily shifts per employee with base salary [ActualCost] and NI Category in seperate coloums as seen below. 

Initial idea is to do a SWIFT/IF statement related to a seperate NI Cateogry table, however I cant get my head around it as the daily costs needs to be summed into Weekly costs in order to calculate the correct NI payable. 

Thank you so much in advance. 

 


DAX
if
National Insurance
Rates
switch
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Molin 
    I had the chance today to look into your file and write some code. I will send you the file in a private message. Following is the description of the solution. However, I still have doubts on whether to consider the calculations on weekly basis or accomulated monthly bases. At the end I decided to go with the weekly based calculation.
    The first step was to import the tax lookup table 

    Then created the relationship.

    Created the measures

     

    Actual Cost = SUM ( LaborCost[ActualCost] )
    NI Measure = 
SUMX ( 
    CROSSJOIN ( VALUES ( DateTable[YYYYWW] ), VALUES ( LaborCost[PayrollId] ) ),
    VAR Cost = [Actual Cost]
    VAR R1 = CALCULATE ( VALUES ( Tax[R1] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    VAR R2 = CALCULATE ( VALUES ( Tax[R2] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    VAR R3 = CALCULATE ( VALUES ( Tax[R3] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    RETURN
    SWITCH(
        TRUE(),
        Cost < 120, 0,
        Cost >= 120 && Cost <= 184, ( Cost - 120 ) * R1,
        Cost > 184 && Cost <= 967, 64 * R1 + ( Cost - 184 ) * R2,
        64 * R1 + 783 * R2 + ( Cost - 967 ) * R3 
    )
)

     

    This is how the report looks like

7 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Molin 

    you can iterate ove the values of week number (preferably, year week number) in the date table. Example

    SUMX (

    VALUES (DateTable[YearWeek]),

    CALCULATE (

    SWITCH(

    TRUE(),

    SELECTEDVALUE(Table[NICategory])="A",

    (SUM ( Table[ActualCost])-184)*0.12)

    )

    ofcourse your calculation would be much more complicated. If you can sample file I migt be able to help you further. However, this is the general idea that you may follow. Other option is to use SUMMARIZE

     

    • Molin's avatar
      Molin
      Icon for Helper I rankHelper I
      4 years ago

      Hi temerj1, 

      Thanks for helping out, that means alot! Iterating Year-Week number makes sense, however its getting quite complicated with the various ranges. 

      How do I send you a sample file? 

      Thanks in advance. 

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Molin 

        You can upload to WeTransfer for example and share the link. 

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Molin 
    I had the chance today to look into your file and write some code. I will send you the file in a private message. Following is the description of the solution. However, I still have doubts on whether to consider the calculations on weekly basis or accomulated monthly bases. At the end I decided to go with the weekly based calculation.
    The first step was to import the tax lookup table 

    Then created the relationship.

    Created the measures

     

    Actual Cost = SUM ( LaborCost[ActualCost] )
    NI Measure = 
SUMX ( 
    CROSSJOIN ( VALUES ( DateTable[YYYYWW] ), VALUES ( LaborCost[PayrollId] ) ),
    VAR Cost = [Actual Cost]
    VAR R1 = CALCULATE ( VALUES ( Tax[R1] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    VAR R2 = CALCULATE ( VALUES ( Tax[R2] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    VAR R3 = CALCULATE ( VALUES ( Tax[R3] ), CROSSFILTER ( LaborCost[NICategory], Tax[Category], BOTH ) )
    RETURN
    SWITCH(
        TRUE(),
        Cost < 120, 0,
        Cost >= 120 && Cost <= 184, ( Cost - 120 ) * R1,
        Cost > 184 && Cost <= 967, 64 * R1 + ( Cost - 184 ) * R2,
        64 * R1 + 783 * R2 + ( Cost - 967 ) * R3 
    )
)

     

    This is how the report looks like