Summing a fee column if its corresponding month column matches the month column in a separate sheet

[EricBainbridge]

Apologies if the below is stupid, but new to AppSheet. 

I am trying to make a stacked column chart to show monthly fees, we have two fee instances that happen on different dates for one instruction for our services. These dates and the fee for each is in the main table and they are within the respective row for the instruction and are inputted by a user when they add a new instruction (there is also a load of other metadata for other processes in the app). 

I am trying to calculate the monthly totals for each fee to display a stacked column chart to show monthly invoicing. 

I have created a second table with a column (Month) and dates in the format "MMMM YYYY" ([XXXX Month] Columns are also shown in this format as this is a v column calculated from the user inputted dates)

From my research I believe SUM(SELECT is the correct function and I tried the below, I get errors if I use any other format than the below;

V Column 1:

SUM(SELECT(Fees[Submission Fee], ([Month] = Fees[_THISROW].Fees[Submission Month])))

V Column 2:

SUM(SELECT(Fees[Determination Fee], ([Month] = Fees[_THISROW].Fees[Determination Month])))

However, no data is generated. As a test I tried it in a virtual column in the main sheet to see if it is just the table reference on the '[_THISROW].[XXXX Month]'. This sort of worked but as anticipated created duplicate lists in the rows of the column where there was an instruction that shared a fee month with another, could someone please advise where I am going wrong/point me in the right direction? 

Much appreciated,

[Joseph_Seddik · Accepted solution]

Welcome to the community!

First you have a Months table, with a Month column that should be the Key column  of your table.

In your other Fees table where you have the dates, add a new physical column named Month, with the following properties:

• Type: Ref, pointing to the Months table. 
• App formula: (supposing that in both table you have the same format YYYY MM)
  
  YEAR([dateColumn]) & " " & RIGHT("0" & MONTH([dateColumn]), 2)

Upon saving you'll see that AppSheet has generated a virtual reverse-reference column named Related Fees. To calculate the monthly fees in a virtual column in the Months table, you can use the following expressions in an app formula:

SUM([Related Fees][Submission Fee]) or SUM([Related Fees][Determination Fee])

Read these guides:

References between tables - AppSheet Help 
Display referenced records - AppSheet Help
Dereference expressions - AppSheet Help
Text expressions - AppSheet Help

[EricBainbridge]

Thank you @Joseph_Seddik works like a charm with a minor modification, I just changed;

YEAR([dateColumn]) & " " & RIGHT("0" & MONTH([dateColumn]), 2) to

TEXT([dateColumn], MMMM YYYY)

So that the month shows up in text rather than the 1-12 system, much appreciated, and thank you for the links to further reading.