Is it possible to create formulaticaly a list of months (1 per row) in a table?

1

I couldn’t find a way to do this. I have another table called [Invoices], which includes a ‘Pay Month’ column displaying text values like ‘June/2025’.

What I want is to filter those values to show only unique entries, one per row, in a new table.

Is that possible?

If not I would have to write manually. [Invoices] display only the last 6 months, so would be convenient to have them synced.

2

Hi Henrique,

I guess youu want to also aggregate the amounts of the invoices to belong to the same month?

Regarding the table with the unique entries, do you want to show only the last 6 months or all of them?

Depending on the answers my answer would change a bit.

It would also be helpful if you could share a dummy doc.

Pablo

3

Hi Pablo,

I just want to fetch the ‘ROI.Month’ table with the unique values of ‘Invoices.Pay Month’, so I don’t have to entry manually.

Looks like an easy task, but I’m struggling with it!

4

I’ve put together a suggestion if you need one. There are several ways to proceed, here is one of them

1 Like
5

If it was only about aggregating data, grouping would probably have been enough. But since you want to do calculations based on the aggregated data it is cleaner to have two tables as you suggested.

So I just copied your data and did the following:

  • Changed your ‘Pay month’ column from text to date: You can still display it in a similar way as before and now it’s much easier to filter and sort accordingly.
  • Added a button to keep the tables in sync: It takes the unique ‘Pay months’ from the ‘Invoices’ table and checks if they exist in the ‘ROI’ table. If they don’t, new rows are created. You can create an automation to push this button daily for example.
Invoices.[Pay Month].Unique().Filter(ROI.Month.Contains(CurrentValue).Not()).ForEach(
    ROI.AddRow(Month,CurrentValue)
)
Let me know if this helped!

Pablo

1 Like
6

The button works perfectly, thank you!

I’ll look into setting up an automation, I haven’t needed that feature until now.

1 Like
7

That’s some crazy formula I would never manage to pull off!

Pablo’s button idea I think is the best choice since I can automate, but thanks for the formula.

8

Okay, no worries.
The table is dynamic, so no automation is required. You create 100 rows, and after a month, add them and it’s generated. You’re good for 100 months in advance.

1 Like