Financial Goals - Best Way to capture the difference from $ made and goal per Month & Year

[Cole_Wedemeier]

Hello!

I'm creating a base that will capture all of my sales. The base consists of a customer table, products table, sales table, and I'd like a goals table. 

Ideally, the goals table would be have a column for the month/year, my goal amount of sales for that month, the actual sales made, and then a column that tells me how far from (under or over) the goal I am for that month. Similarly, I'd like to do the same for yearly goals, where it can show me the yearly goal total (based on the sum of each month in that year I'd set), what my actual sales for the year are, and how far from my goal (over or under) I am. 

My sales table already captures mm/yyyy and individually the month & year for the date of the sale so I'm pretty positive I can link a sale to the monthly goal record based on that month/year field with an automation.

I'm just hitting roadblocks with how I show both monthly and yearly in the same table. I'm sure I can do a view to show me just the monthly or just the yearly if I can figure out how to get the two time periods of goal, actual, and difference fields to work out. 

Any ideas or suggestions are welcome. Thank you! 

[TheTimeSavingCo]

Hmm, so in the "Goals" table, would you be alright with having the records like so?

Apologies, I don't really understand the problem you're facing so figured I'd throw something out there and you let me know what's wrong with it heh

[Cole_Wedemeier]

Adam,

Thanks! This is what I did end up setting up for monthly, but where I'm still stuck is how could I do something that would also allow a yearly round up. 

Ideally, the yearly would take a rollup of each month's goal amounts to get the total Yearly Goal. Then I could do the same rollup for the year, similar to how I did for sales, of the actual total $ sales made for that year. The formula for the yearly difference between the goal and the actual would work too. 

I hope that makes more sense. Since the goal figures per month are already there, I don't want to have to calculate the yearly goal, but have it be the sum of each month. That way if I update the goal for 1 month, the goal for the year is automatically updated as it's a sum of all 12 months. 😎

[TheTimeSavingCo · Accepted solution]

Hm, so something like this?

Here's a link to the base

Idea is to create a formula field that'll output `[MONTH] [YEAR]` and `Year [YEAR]`, then use an automation to paste it into a linked field which will link everything together as needed:

For the rollup, I created a linked field in the "Goals" table that links to itself and link all the months for that year to a record that represents that year, then created a rollup field that would either display the summed values or the value from "Goal - Manual" if it was present

[Cole_Wedemeier]

@TheTimeSavingCo Thank you! This worked wonderfully! I hadn't realized we could link within a table. I had always just had it in my mind we could only link outside of that table. 

I really appreciate your time. 

[TheTimeSavingCo]

Glad I could help!