Formula based on month

Question

I have no idea how to even ask this question. I want to create a formula. Say our FY starts in October, so October is month 1, November month 2, December month 3...and so on. In excel I have the following formula =ROUND(((T9/$D$36)*$D$35),0)

Where T9 is the annual bonus amount. D36 is 12 months and D35 is the number of months...Manually I go into excel and change D35, so if it were July D35 would have a 10 in the cell.

How do I create a formula that changes the months based on current month and our FY without any manual manipulation?

Ankur_BDO · Answer

Since your question requires a different month order based on the FY, create a table in Text Input with Calendar Month order and provide ID as per your FY. Now Join this table to the Data table where the actual Amount is provided with Dates. Map them using the Calendar Month, This will provide the multiplying factor to each amount. finally apply your Formula in Formula tool. (I have used a Datetime parse tool as my dates were initially given as string) Check the attached workflow for the working solution.

20240823_CalculateBonus.yxmd

OTrieger · Answer

@Haley_Pianalto 
There are different ways to get the month:
1. through User Interface: ask the user to indicate which month to be used for the calculation.
2. If you know that the data will be process on the same month for the bonus calculation you can use Today() and you will get today date, from it you could get out the month DateTimeformat([Today),"%B")
Then you can based on the month assign a number, or even easier create a table that indicating what is the value for each month then Join based on the month and you will get the value
Then in a formula Round([Month]*[Bonus]/12,0)