Skip to main content

I have a table with all our deals that contains the following information





  • Total amount of deal


  • Start date


  • End date




I would like to split this up to a monthly revenue per deal for invoicing and reporting. Most of these deals have a duration of 1 year, but sometimes less. So for example, one row might look like this:





  • 6000


  • 1 January 2022


  • 31 December 2022




I would then like to be able to build a report that has for columns (like a pivot table) for all the months from January to December and then an amount of 500 for this deal.



Something like this is fairly easy in Excel, but I’m not quite sure how to build this in Airtable.

Hi @Youri_Voet ,



Welcome to Airtable Community !



First of all you need to have a field for each month. Then I’m guessing you need it to read for the current year only? This field will have the following formula:



IF(AND(YEAR({Start date})&MONTH({Start date})<YEAR(NOW())&MONTH(NOW()),MONTH({Start date})=1),{Total amount of deal}/DATETIME_DIFF({End date},{Start date},"Months"))


Then for each month change this part “MONTH({Start date})=1” into the month number



Does this help?

Hi @Youri_Voet ,



Welcome to Airtable Community !



First of all you need to have a field for each month. Then I’m guessing you need it to read for the current year only? This field will have the following formula:



IF(AND(YEAR({Start date})&MONTH({Start date})<YEAR(NOW())&MONTH(NOW()),MONTH({Start date})=1),{Total amount of deal}/DATETIME_DIFF({End date},{Start date},"Months"))


Then for each month change this part “MONTH({Start date})=1” into the month number



Does this help?


Hi Mohamed,



Thank you very much.



A field for every month is definitely a solution and something that I would do in Excel, it just doesn’t really feel like a very elegant solution in Airtable. Or can you easily create many fields like that, because with 4 years of data I will already have to create 48 separate fields for this.



If you think this is the most elegant solution, then I will go for that.

Hi Mohamed,



Thank you very much.



A field for every month is definitely a solution and something that I would do in Excel, it just doesn’t really feel like a very elegant solution in Airtable. Or can you easily create many fields like that, because with 4 years of data I will already have to create 48 separate fields for this.



If you think this is the most elegant solution, then I will go for that.


I do think this is the best solution with the data I have now. But for example, if you need to rollup the numbers for the revenue for each month, then a better solution would be Linked Records where you have the Month and Year in another table as records

Reply


Terms & Conditions