Vacation overview across years

[HEW]

Hi.

We are trying to create an overview in Power BI, where all employees can follower their vacation balance.

In Denmark the vacation is earned (example) from September 1 2020 - August 31 2021 and you earn 2,08 day per month. You can spend your vacation from September 1 2020 - December 31 2021. At the moment we have an Excel caluculation and we wish to have a similar view in Power BI. However, I find it vary hard to calculate the running balance as the balance from previous years September 2021 should show the closing balance from December 2021 - 4 months later. 

Pls. see below:

Any suggestions how to handle this? Normally the balance from previous years would be 3,34 days in the right table as this table begins in September, but we should use the balance of December... And as John only have 3,34 days left in the first year, the remaining 1,66 days (5 days of vacation in October) should be deducted in the new vacation year.

Thanks a lot in advance.

Br.

Helen

[Anonymous]

Hi @HEW ,

Why does the prior year's balance not match the current year's balance? How to calculate it? Please provide details and expected output?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HEW]

Hi Polly.

The vacation system in Denmark is a bit strange - thats why the closing balance December first year is the opening balance September (3 months earlier...).

I found a work around by creating new tables for current and next vacation year  and this way it was possible.

Thanks.

[HEW]

Anyone? 🙂

[lbendlin]

This is too complex for Power BI.  You may need to do this in a real database instead.

[HEW · Accepted solution]

Hi.

I found a work around by creating new tables for current and next vacation year  and this way it was possible.

Thanks.

[lbendlin]

Did they spend both 3.34 and 1.66 in October 2021?  Who decides in which year that spend is recorded?

[HEW]

They spend 5 days in October, but as they only have 3,34 days left in this vacation period (left table), the remaining 1,66 days will be deducted in the next vacation period (right table)...