Skip to main content

Getting monthly revenue based on a row with start and end date

  • August 22, 2022
  • 3 replies
  • 59 views
Y
Forum|alt.badge.img+1

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.

    3 replies

    Mohamed_Swella1
    Forum|alt.badge.img+17
    • Mohamed_Swella1
    • Inspiring
    • 1030 replies
    • August 22, 2022

    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?

    Y
    Forum|alt.badge.img+1
    • Youri_Voet
    • Author
    • New Participant
    • 2 replies
    • August 22, 2022

    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.

    Mohamed_Swella1
    Forum|alt.badge.img+17
    • Mohamed_Swella1
    • Inspiring
    • 1030 replies
    • August 22, 2022

    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


    Terms & ConditionsAccessibility statement