# Actual vs Forecast revenue question

How can I calculate an actual revenue (based on events in the past) and a forecast revenue (based on events in the future) ?

• Actual Revenue: would be a sum of sales, yes? Assuming that you are recording sales in a table, then you can use a rollup column to sum all the amounts.
• Forecast Revenue: how would you calculate this if you were to do it manually?
1 Like

Hello Daren

actual = sum of sales up to today
forecast = sum of sales coming in the future (assuming I have a date for each event)

Dear Daren

Have you any tips to cover my request?
Basically I record events and split the revenue by participants
I need to report on events and revenue older then today and also report on events and revenue planned in the future
Hope my explanations are clear

Thank you

Can you show us a sample of how your data looks like?

Done

Which table in the file should I take a look at? And which columns are you using to reference the revenue?

Shows table : âDate for the showâ
Zicos : âTip for the zicosâ (musician revenue for a specific show)
MyBalance : dashboard for actual revenue vs forecast (sum of credit vs sum of debits)
MyDebs : store all debits made by a musician

actual revenue = sum of all the âTip for the showâ for a musician participating to a show (âParticipantâ) up to now
forecast = sum of all the âTip for the showâ for a musician assigned to a show (âParticipantâ) planned in the future (based on date for the show)

Hi Experts,

I guess I need to filter the result of a Rollup field based on datesâŚ can this be done?

thanks

In the Zicos table, how do you know which show is the tip for or does it not matter? Do those tips for a specific musician or for all musicians and then you divide them later?

In Zicos, âShows Participation IDâ relate to Shows table ârow IDâ.
Tips are recorded for specific musicians.

Assuming rows in Users are musicians you want to calculate the revenue for.

Step 1: In Shows, create an If-Then-Else column: âFuture?â. If âDate for the Showâ is after now then true, else false.

Step 2: In Zicos, create a lookup table using the âShows Participation IDâ (I would recommend renaming this to Shows ID, itâs the ID of the show and adding the word âparticipationâ in there would be confusing) back to the Shows table.

Step 3: Use a lookup column to pull the âFuture?â column into the âZicosâ table.

Step 4: In the âZicosâ table, create an âActual Revenueâ column. If âFuture?â is checked then 0, else return the âTip for the Zicosâ value.

Step 5: In the âZicosâ table, create a âFuture Revenueâ column. If âFuture?â is checked then return the âTip for the Zicosâ value, else return 0.

Step 6: In the Users table, create a relation using the name of the user to the âParticipantâ column in the âZicosâ table (though this is not recommended, you should store the rowID of the user in the âZicosâ table and use that for the relation instead of the name, which may not be unique). Make it a multiple relation.

Step 7: Create a rollup on top of the relation, sum the âActual Revenueâ column. This will be the sum of actual revenue for the musician.

Step 8: Create a rollup on top of the relation, sum the âFuture Revenueâ column. This will be the sum of future revenue for the musician.

3 Likes

Thank you very much for the tips, I will give a try

Cheers

1 Like

You are my Hero !!!

Thank you so much for your support and patience

Cheers

1 Like

So it worked! Good to hear.