powerbi_newb
New Member
- Joined
- Mar 19, 2017
- Messages
- 3
Good day,
This is the spreadsheet link: CLICK HERE
I am currently looking for a formula and the formula that I am looking for goes something like this.
Each member has a set of quota per month. The quota is a fix value, and will never be changed. A member will use his previous month quota prior to the current month. And if both previous month and current month quota is not enough, then it will now borrow from the next month quota.
A logical example, and in numerals. I will be using the data available on the spreadsheet.
BB - Made a total of 10 entries this month, and last month he only made 2 entries.
BB - Has only a maximum of 5 quota per month. So putting them into equation it should look like this.
Previous month - 5 - 2 = 3 available entries to be use for next month.
Current month - (10 - 3(previous month)) - 5 (current quota) = 2 entries spill over or exceeded.
Next month - 2 - 5 = 3 remaining quota for the next month.
So in total, BB will have 3 of remaining entries for the next month.
Column H - We want to output the remaining quota from the previous month. If it goes negative, return it as 0. As the rule, a borrow will be applied.
I am thinking of something like this, also is there a way to improve this?
Column J - This is where we want to output the previous month + current month, if exceeded then it will now borrow from the next month quota.
This is where I struggle, I really do not know how should I output and apply the borrow rule.
Thank you, I would really appreciate any kind of help.
This is the spreadsheet link: CLICK HERE
I am currently looking for a formula and the formula that I am looking for goes something like this.
Each member has a set of quota per month. The quota is a fix value, and will never be changed. A member will use his previous month quota prior to the current month. And if both previous month and current month quota is not enough, then it will now borrow from the next month quota.
A logical example, and in numerals. I will be using the data available on the spreadsheet.
BB - Made a total of 10 entries this month, and last month he only made 2 entries.
BB - Has only a maximum of 5 quota per month. So putting them into equation it should look like this.
Previous month - 5 - 2 = 3 available entries to be use for next month.
Current month - (10 - 3(previous month)) - 5 (current quota) = 2 entries spill over or exceeded.
Next month - 2 - 5 = 3 remaining quota for the next month.
So in total, BB will have 3 of remaining entries for the next month.
Column H - We want to output the remaining quota from the previous month. If it goes negative, return it as 0. As the rule, a borrow will be applied.
I am thinking of something like this, also is there a way to improve this?
Code:
[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MAX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]5[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]-[/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]$F2[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]Column J - This is where we want to output the previous month + current month, if exceeded then it will now borrow from the next month quota.
This is where I struggle, I really do not know how should I output and apply the borrow rule.
Thank you, I would really appreciate any kind of help.
