Using 'Date' field to label by Month

[Alyssa_Martin]

Hello

I’m new around here, but I’m trying to set up a base that automatically breakdowns revenue, profit, owner’s page, expenses, etc using the Profit First methodology by Mike Michalowicz.

I feel like I’ve done pretty well so far. Here’s a look at the most important table that I’m trying to create.

And now I want to automate it more. So, here’s a link to the whole base so you can see how it’s set up (so far):

Airtable

Profit First Calculator copy - Airtable

Explore the "Profit First Calculator copy" base on Airtable.

I’ve set up a Zapier connection so that Stripe automatically updates payments in the first table, labelled “Client Payments.”

Then the next table (“Monthly Revenue Totals”) calculates the total revenue for each month, based on what Stripe has recorded in the first table.

The last table (“Profit First calculations”) takes the total revenue for each month & applies the Profit First formulas to it, so that I can easily see how much I’ve got to, say, take as my own wage from the business bank accounts.

But there’s still too much manual work involved for my liking. That’s where I’m stuck & would love your advice…

Stripe records the date of the transaction. But to get AirTable to calculate the total earned for the month, I have to manually tag the correct month in another column. I’m wondering if there’s a formula or something to do that automatically.

Something like: if the date is 18 September 2017, select “Sep” in the next column.

If there’s a better / smarter way to achieve that, I’m all ears :slightly_smiling_face:

P.S. If you want to know more about the Profit First methodology, it’s all outlined in this book:

https://profitfirstbook.com/

[Julian_Kirkness]

Hi @Alyssa_Martin

You could use Zapier to do this (I assume you mean linking the Client Payment to the Month in Monthly Revenue Totals). You could either extend the same Zap or create another.

Triggering on the new record, you would then use a Formatter (Date) to format the date to a custom format (MMM) which will return a 3 character Month. You then have an update step which would then Update the Monthly Revenue Totals field in the original record.

You should also consider what happens next year - it may be better to have Year and Month in the Monthly Revenue Totals table so that the same process can continue beyond December.

You could also use Blocks to give you monthly totals as charts or cross tabs - which may be simpler…

Hope this helps?

[Alyssa_Martin]

Thanks @Julian_Kirkness.

I had been wondering the best way to deal with the year, but now I realise I can do this all in one base without having to create a new base every New Year. Thanks for the lightbulb moment.

I’m in the process of setting up the Zap formatting, like you suggested. I have got it 90% of the way there, but it’s still not quite there. I’ll keep going and see if I can figure it all out. Really appreciate your ideas.

[Alyssa_Martin]

For anyone following along at home, I figured out the Zap and wanted to share it with you.

This is a 5 step sequence (which means you need a paid Zapier account, apparently) and it looks like this…

And then each date formatting template looks like this…

I’ve tested it and it works. Formats the dates perfectly.

Hope this helps.

[Aly_Johnson-Kur]

Hi Alyssa (another Alyssa here),

I originally came to this thread looking to answer the question about how to label by month, but in looking at your base, I’m now curious how you formatted the cards in your gallery view. How did you get them to show up as month by month, and to tally your metrics for each month?

Thanks!
A

[Crystal_Beasley]

I’m digging though your base that you shared last October and it’s already been super helpful. I’m wondering if you’d be willing to share the more tricked out version you’re using now?

Best,
c

[Alyssa_Martin]

I’m honestly not sure I remember, because I’ve tried and failed to duplicate it in other bases. But what I think I did is…

I created the Profit First Calculations tab and had to make sure the first column was “month, year.” (Then I could use that to link to other records too.)

But in the Profit First Calculations tab, I also had to create a column that I labelled “Year.” Finally, I created a Gallery view for 2019 and filtered by the “year.”

[Alyssa_Martin]

I’m very late in seeing & responding to this, but here you go. (You’ve given me the kick up the butt I needed to get back in the habit of using this base!)

Airtable

Profit First Calculator copy - Airtable

Explore the "Profit First Calculator copy" base on Airtable.