[Solved] Going crazy, not getting positive results

1

Hello everyone,
First of all, thank you for reading this question and spending time thinking about it.

I don’t know if what I want to do is even possible. I’ll try to summarise. I have three tables:

Month

with a single column named Month with a row per each month:

  • 2019-01-01
  • 2019-02-01
  • 2019-03-01

And so on…

Monthly money

This table is the invoices table with the following columns:

  • Month (Lookup from Month table)
  • Client
  • Project
  • Estimate
  • Invoice

Please note: There can be multiple rows with the same “Month” and I use the Row grouping to see the total money incoming each month.

Costs

This table is a list of the costs per month but I would like to have a column with the reference of the incoming money. The columns I have are:

  • Month (Lookup from Month table)
  • Cost 01 name
  • Cost 01 amount
  • Cost 02 name
  • Cost 02 amount
  • Cost 03 name
  • Cost 03 amount
  • Total (Sum row with the sum of the previous amounts)
  • Total income (Formula. See below)

Please note: This table has one row per month, that’s why I want to have a column with the total of the income.

Total income formula

As I want to display a graph with two lines, one for the costs and one for the income, I wanted to include the “total income” here.

I’ve tried the following formulas and neither of them work:

01

[Monthly money].Invoice.Filter([Monthly money].Month.Contains(thisRow.Month)).Sum()

The problem of this formula is that the result is the total of the whole column Invoice, so it doesn’t Filter.

02

[Monthly money].Invoice.Filter([Monthly money].Month.Contains(Costs.Month)).Sum()

Same result as the previous formula.

03

[Monthly money].Invoice.Filter([Monthly money].Month=thisRow.Month)

In this case, there’s no result, only empty brackets [ ]

Any help will be extremely appreciated.

Thanks for reading.

EDIT: Solved thanks to @Daniel_Stieber formula:
[Monthly money].Filter(Month = thisRow.Month).Invoice.sum()

2

Dear @Pau_Valiente,

Welcome to this great community :handshake:

I recommend to share your dummy doc, to make it much more easily to get the support to make your doc working.

2 Likes
3

Hey @Pau_Valiente ,

As @Jean_Pierre_Traets said, it is always easier to understand your need, if you already share your current state :slight_smile:

I made something, but I’m not sure if it solves your need, please check.

2 Likes
4

try using this formula:
[Monthly money].Filter([Monthly money].Month=(thisRow.Month)).Invoice.Sum()

2 Likes
5

Hi Jean Pierre and Daniel, that’s true, I didn’t think on sharing a dummy document. Sorry.

Daniel, your example is exactly as I described and I see where was my error. I did point the table and column before the Filter action and I can see you have pointed to the table first, then the Filter action and then the column + Sum at the end. :+1:t3:

Exactly as Asaf pointed out too except the Filter expression includes only the column name, not the table.

Thank you very much the three of you. I was going crazy :crazy_face:

2 Likes
6

Hi @Pau_Valiente,

Great result thanks to many openminded community members :gem:
Now you are also part of this group and we are looking forward for your input and inspiration, as all of us have a unique background and perspective.

:bulb: Have a look at the templates that you can easily add in your doc, from how others have solved certain challenges, you will be able to gain fast access to the know how. :bulb:

7

Thanks, I’ll try to help as much as I can, but the level is really high. :sweat_smile: