title: BIGQUERY YTD Sum not calculating properly
body: <p>I have a query result UNION' d with a calendar table and the results are as below.
  I want to add a YTD calculation to it as below but the results I am expecting are not correct.</p>
  <pre><code>SELECT *,
  sum(AMOUNT) over (partition by FISCAL_YEAR, GL_ACCOUNT, WORKCENTRE_CODE, UNIT_OF_MEASURE order by FISCAL_MONTH ) as YTD_AMOUNT
  FROM
  (SELECT * FROM QUERY 1
  UNION with SELECT * FROM QUERY 2)
  
  
  </code></pre>
  <p>Result of above query:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Version</th>
  <th>WCODE</th>
  <th>ACC</th>
  <th>UOM</th>
  <th>FISCAL_MONTH</th>
  <th>FISCAL_YEAR</th>
  <th>CALENDAR_DATE</th>
  <th>CALENDAR_MONTH</th>
  <th>AMOUNT</th>
  <th>DATE_YYYYMM</th>
  <th>YTD_AMOUNT</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>1</td>
  <td>2021</td>
  <td>31/07/2020</td>
  <td>202007</td>
  <td>565.95</td>
  <td>202007</td>
  <td>565.95</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>1</td>
  <td>2021</td>
  <td>31/07/2020</td>
  <td>202007</td>
  <td>0</td>
  <td>202007</td>
  <td>565.95</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>2</td>
  <td>2021</td>
  <td>31/08/2020</td>
  <td>202008</td>
  <td>569.95</td>
  <td>202008</td>
  <td>1135.9</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>2</td>
  <td>2021</td>
  <td>31/08/2020</td>
  <td>202008</td>
  <td>0</td>
  <td>202008</td>
  <td>1135.9</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>3</td>
  <td>2021</td>
  <td>30/09/2020</td>
  <td>202009</td>
  <td>1190.7</td>
  <td>202009</td>
  <td>2326.6</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>3</td>
  <td>2021</td>
  <td>30/09/2020</td>
  <td>202009</td>
  <td>0</td>
  <td>202009</td>
  <td>2326.6</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>4</td>
  <td>2021</td>
  <td>31/10/2020</td>
  <td>202010</td>
  <td>664.85</td>
  <td>202010</td>
  <td>2991.45</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>4</td>
  <td>2021</td>
  <td>31/10/2020</td>
  <td>202010</td>
  <td>0</td>
  <td>202010</td>
  <td>2991.45</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>5</td>
  <td>2021</td>
  <td>30/11/2020</td>
  <td>202011</td>
  <td>606.7</td>
  <td>202011</td>
  <td>3598.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>5</td>
  <td>2021</td>
  <td>30/11/2020</td>
  <td>202011</td>
  <td>0</td>
  <td>202011</td>
  <td>3598.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>6</td>
  <td>2021</td>
  <td>31/12/2020</td>
  <td>202012</td>
  <td>826.7</td>
  <td>202012</td>
  <td>4424.85</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>6</td>
  <td>2021</td>
  <td>31/12/2020</td>
  <td>202012</td>
  <td>0</td>
  <td>202012</td>
  <td>4424.85</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>7</td>
  <td>2021</td>
  <td>31/01/2021</td>
  <td>202101</td>
  <td>871.3</td>
  <td>202101</td>
  <td>5296.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>7</td>
  <td>2021</td>
  <td>31/01/2021</td>
  <td>202101</td>
  <td>0</td>
  <td>202101</td>
  <td>5296.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>8</td>
  <td>2021</td>
  <td>28/02/2021</td>
  <td>202102</td>
  <td>847.4</td>
  <td>202102</td>
  <td>6143.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>8</td>
  <td>2021</td>
  <td>28/02/2021</td>
  <td>202102</td>
  <td>0</td>
  <td>202102</td>
  <td>6143.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>9</td>
  <td>2021</td>
  <td>31/03/2021</td>
  <td>202103</td>
  <td>1225.45</td>
  <td>202103</td>
  <td>7369</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>9</td>
  <td>2021</td>
  <td>31/03/2021</td>
  <td>202103</td>
  <td>0</td>
  <td>202103</td>
  <td>7369</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>10</td>
  <td>2021</td>
  <td>30/04/2021</td>
  <td>202104</td>
  <td>798.55</td>
  <td>202104</td>
  <td>8167.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>10</td>
  <td>2021</td>
  <td>30/04/2021</td>
  <td>202104</td>
  <td>0</td>
  <td>202104</td>
  <td>8167.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>11</td>
  <td>2021</td>
  <td>31/05/2021</td>
  <td>202105</td>
  <td>936.2</td>
  <td>202105</td>
  <td>9103.75</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>11</td>
  <td>2021</td>
  <td>31/05/2021</td>
  <td>202105</td>
  <td>0</td>
  <td>202105</td>
  <td>9103.75</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>12</td>
  <td>2021</td>
  <td>30/06/2021</td>
  <td>202106</td>
  <td>0</td>
  <td>202106</td>
  <td>9103.75</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected Result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Version</th>
  <th>WCODE</th>
  <th>ACC</th>
  <th>UOM</th>
  <th>FISCAL_MONTH</th>
  <th>FISCAL_YEAR</th>
  <th>CALENDAR_DATE</th>
  <th>CALENDAR_MONTH</th>
  <th>AMOUNT</th>
  <th>DATE_YYYYMM</th>
  <th>YTD_AMOUNT</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>1</td>
  <td>2021</td>
  <td>31/07/2020</td>
  <td>202007</td>
  <td>565.95</td>
  <td>202007</td>
  <td>565.95</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>1</td>
  <td>2021</td>
  <td>31/07/2020</td>
  <td>202007</td>
  <td>0</td>
  <td>202007</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>2</td>
  <td>2021</td>
  <td>31/08/2020</td>
  <td>202008</td>
  <td>569.95</td>
  <td>202008</td>
  <td>1135.9</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>2</td>
  <td>2021</td>
  <td>31/08/2020</td>
  <td>202008</td>
  <td>0</td>
  <td>202008</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>3</td>
  <td>2021</td>
  <td>30/09/2020</td>
  <td>202009</td>
  <td>1190.7</td>
  <td>202009</td>
  <td>2326.6</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>3</td>
  <td>2021</td>
  <td>30/09/2020</td>
  <td>202009</td>
  <td>0</td>
  <td>202009</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>4</td>
  <td>2021</td>
  <td>31/10/2020</td>
  <td>202010</td>
  <td>664.85</td>
  <td>202010</td>
  <td>2991.45</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>4</td>
  <td>2021</td>
  <td>31/10/2020</td>
  <td>202010</td>
  <td>0</td>
  <td>202010</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>5</td>
  <td>2021</td>
  <td>30/11/2020</td>
  <td>202011</td>
  <td>606.7</td>
  <td>202011</td>
  <td>3598.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>5</td>
  <td>2021</td>
  <td>30/11/2020</td>
  <td>202011</td>
  <td>0</td>
  <td>202011</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>6</td>
  <td>2021</td>
  <td>31/12/2020</td>
  <td>202012</td>
  <td>826.7</td>
  <td>202012</td>
  <td>4424.85</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>6</td>
  <td>2021</td>
  <td>31/12/2020</td>
  <td>202012</td>
  <td>0</td>
  <td>202012</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>7</td>
  <td>2021</td>
  <td>31/01/2021</td>
  <td>202101</td>
  <td>871.3</td>
  <td>202101</td>
  <td>5296.15</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>7</td>
  <td>2021</td>
  <td>31/01/2021</td>
  <td>202101</td>
  <td>0</td>
  <td>202101</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>8</td>
  <td>2021</td>
  <td>28/02/2021</td>
  <td>202102</td>
  <td>847.4</td>
  <td>202102</td>
  <td>6143.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>8</td>
  <td>2021</td>
  <td>28/02/2021</td>
  <td>202102</td>
  <td>0</td>
  <td>202102</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>9</td>
  <td>2021</td>
  <td>31/03/2021</td>
  <td>202103</td>
  <td>1225.45</td>
  <td>202103</td>
  <td>7369</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>9</td>
  <td>2021</td>
  <td>31/03/2021</td>
  <td>202103</td>
  <td>0</td>
  <td>202103</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>10</td>
  <td>2021</td>
  <td>30/04/2021</td>
  <td>202104</td>
  <td>798.55</td>
  <td>202104</td>
  <td>8167.55</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>10</td>
  <td>2021</td>
  <td>30/04/2021</td>
  <td>202104</td>
  <td>0</td>
  <td>202104</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>11</td>
  <td>2021</td>
  <td>31/05/2021</td>
  <td>202105</td>
  <td>936.2</td>
  <td>202105</td>
  <td>9103.75</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>11</td>
  <td>2021</td>
  <td>31/05/2021</td>
  <td>202105</td>
  <td>0</td>
  <td>202105</td>
  <td>0</td>
  </tr>
  <tr>
  <td>A</td>
  <td>333333</td>
  <td>3005</td>
  <td>hours</td>
  <td>12</td>
  <td>2021</td>
  <td>#######</td>
  <td>**202106</td>
  <td>0</td>
  <td>202106</td>
  <td>9103.75**</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Ideally I would only like to add the missing month only i.e in this case '202106' and then YTD Calculated for the 12 months.</p>
  <p>The current results in the output layer are aggregating which is incorrect.</p>