Calculating UK National Insurance rates

[Molin]

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. 

 

[tamerj1 · Accepted solution]

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

[tamerj1]

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]

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]

@Molin 

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

[Molin]

Hi temerj1

Here you go. https://we.tl/t-XVGmDP5uD9

Again many thanks!

[v-easonf-msft]

Hi, @Molin 

I've checked your pbix file and there seems to be no better way.

I'm afraid you have to calculate them according to different situations.

Best Regards,
Community Support Team _ Eason

[tamerj1]

@Molin 

Great. Let me go through it. I'll get back to you soon.