topic Calculating UK National Insurance rates in DAX Commands and Tips

Calculating UK National Insurance rates

Molin — Mon, 04 Apr 2022 10:21:40 GMT

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.

Re: Calculating UK National Insurance rates

tamerj1 — Mon, 04 Apr 2022 11:18:49 GMT

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

Re: Calculating UK National Insurance rates

Molin — Mon, 04 Apr 2022 20:06:46 GMT

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.

Re: Calculating UK National Insurance rates

tamerj1 — Mon, 04 Apr 2022 20:37:29 GMT

Molin

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

Re: Calculating UK National Insurance rates

Molin — Mon, 04 Apr 2022 20:57:50 GMT

Hi temerj1

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

Again many thanks!

Re: Calculating UK National Insurance rates

tamerj1 — Mon, 04 Apr 2022 21:06:38 GMT

Molin

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

Re: Calculating UK National Insurance rates

v-easonf-msft — Thu, 07 Apr 2022 08:20:03 GMT

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

Re: Calculating UK National Insurance rates

tamerj1 — Fri, 29 Apr 2022 20:22:39 GMT

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