Accrual calculation with Power PI

[kukszi]

Hi there,

I have employees who are eligible for a bonus, and I'd like to calculate with Power BI how much should I accrue for them.

I know somehow a calendar should be connected to the employee database, but can't figure out the right way to do it.

Employee database looks like this:

Employee ID	Annual bonus
1	$12,000
2	$24,000
3	$36,000

Calendar looks like this:

Month	Period
Jan	1
Feb	2
Mar	3
Apr	4
May	5
Jun	6
Jul	7
Aug	8
Sep	9
Oct	10
Nov	11
Dec	12

Basically what I need to achieve is that I know that the annual bonus is $72,000, but I want Power BI to show me a matrix visual about how much is the YTD and the monthly bonus accrual after each period, similar to this:

Month	Monthly accrual	YTD accrual
Jan	$6,000	$6,000
Feb	$6,000	$12,000
Mar	$6,000	$18,000
Apr	$6,000	$24,000
May	$6,000	$30,000
Jun	$6,000	$36,000
Jul	$6,000	$42,000
Aug	$6,000	$48,000
Sep	$6,000	$54,000
Oct	$6,000	$60,000
Nov	$6,000	$66,000
Dec	$6,000	$72,000
Full Year	$72,000	$72,000

Thanks,

kukszi

[parry2k · Accepted solution]

@kukszi attach file has a solution, you can tweak as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Jihwan_Kim · Accepted solution]

Monthly accrual : =

VAR totalperiod =

CALCULATE ( COUNTROWS ( Months ), REMOVEFILTERS () )

RETURN

SUMX (

VALUES ( Months[Month] ),

DIVIDE ( SUM ( Data[AnnualBonus] ), totalperiod )

)

YTD accrual : =

SUMX (

FILTER ( ALL ( Months ), Months[Period] <= MAX ( Months[Period] ) ),

[Monthly accrual :]

)

Link to the sample PBIX file 

[parry2k · Accepted solution]

@kukszi attach file has a solution, you can tweak as you see fit.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡