calculate budget (starting balance, remaining, carry over)

[aibrahim]

Ok so basically what I’m looking for is to build a table that has the following features:

Columns: month, available balance, hours used, hours remaining (OR overages if the available balance was exhausted).

1- Available balance (monthly fixed allowance) for example 20 hours that will be added EVERY MONTH. This will be a fixed number based on each project. Available balance will also be current allowance (20 hours) PLUS whatever is carried over from previous month.

2- Hours used will be from a Data source to show how many hours logged in that specific month.

3- Remaining balance will be (available balance / monthly allowance - hours used). If result is negative that means it’s overages and no hours will be carried over. If it is positive that means there’s still some hours left in that month and we need to carry it over to the next month’s available balance / hours

Example: in last month (may) allowance balance was 20 hours, we spent 15 hours so we have 5 hours remaining. Next month (June) starting balance should be 20 hours which is fixed / given PLUS whatever is remaining from previous months which is 5. So the starting balance should be 25 in June. If May remaining hours was negative number then the starting balance in May will just be 20 hours and that’s it.

Example: 

[aibrahim]

@v-xiaotang - I crated a video for to make it easy. I would really appreciate your help here. 

Here's the vidoe https://watch.screencastify.com/v/V7Gm2dnUdjTjpr94S3na

[v-xiaotang]

Hi @aibrahim 

Thanks for reaching out to us.

Can you highlight the expected column in your table picture? And it seems the data given in the example above does not appear in the example image below.....  

(In order to help understanding, it would be better if the data involved in the examples are the same, otherwise it will be difficult to map the values to the columns.)

Best Regards,

Community Support Team _Tang

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