Find Previous Month MTD (Month to Date) Calculation in BigQuery

Assuming I have initial table of date and each order value of the date:

date_order  |gross_order|
|---------- |-----------|
|25 Nov     | 50        |
|27 Nov     |300        |
|26 Dec     | 400       |
|29 Dec     |300        |
|30 Dec     |100        |

So I use this query for calculating current MTD (cumulative gross order in a month from day 1 of certain month to certain date in that month),

select date_order, gross_order, sum(gross_order) OVER (PARTITION date_trunc(date_order,MONTH ORDER BY date_order) AS current_mtd

the result will be something like this:

|date_order |gross_order| current_mtd |
|---------- |-----------|-------------|
|25 Nov     | 50        | 50          |
|27 Nov     |300        |350          |
|26 Dec     | 400       |400          |
|29 Dec     |300        |700          |
|30 Dec     |100        |800          |

The idea is to also have value of previous month MTD value for all row in the date range, so in the 25 and 27 Nov, previous MTD value will be 0 because there are no prior gross order value. And in the 30 december row, the value will be sum of gross_order between 1 to 30 november, which give us 50 + 350 = 400

Expected result:

|date_order |gross_order| current_mtd | previous_mtd
|---------- |-----------|-------------|-------------|
|25 Nov     | 50        | 50          |0            |
|27 Nov     |300        |350          |0            |
|26 Dec     | 400       |400          |50           |
|29 Dec     |300        |700          |350         |
|30 Dec     |100        |800          |350         |

I’m kinda stuck as how to calculate the previous mtd value, is there any way to do it? Thank you

Answer

Now that I understand the question, you can use a correlated subquery:

with t as (
      select date '2020-11-25' as date_order,  50  as gross_order union all
      select date '2020-11-27' as date_order, 300  as gross_order union all
      select date '2020-12-26' as date_order, 400 as gross_order union all
      select date '2020-12-29' as date_order, 300  as gross_order union all
      select date '2020-12-30' as date_order, 100  as gross_order 
     )
select t.*,
       (select sum(t2.gross_order)
        from t t2
        where date_trunc(t2.date_order, month) = date_add(date_trunc(t.date_order, month), interval -1 month) and 
              extract(day from t2.date_order) <= extract(day from t.date_order)
       )
from t;

The subquery is matching rows from the previous month (the first condition) and then comparing the date (the second condition).

This works on the data you have supplied.