Dynamic Date Field with Over Function and Balance Forward Question

Sgoedecke

Please see the attached video to better explain what we are trying to accomplish. 

https://youtu.be/jOZmhUea6KE.

Beast Mode Used:

SUM(SUM((CASE 
WHEN `PERIOD` = '2016-01-01' THEN (`Beg Balance`+`AMOUNT`) ELSE `AMOUNT` END)
       )
   ) 

   OVER(ORDER BY `PERIOD`)

 

We currently have a balance sheet account where we are trying to calculate the actual balance using the beginning balance and the current month activity. The Beast mode that is currently written using the Over function (see above) works for the entire date range in the data set. However, we are using a static date (2016-01-01) to bring in the beginning balance at the start of the calculation. The issue we are having is building a dynamic date field so that if we select a different year (i.e. 2017 & 2018) it will use the beginning balance from the first January in the date range selected (January 2017) vs. the static date field of 2016-01-01. Attached is an excel file with example data. Any ideas/suggestion on how to solve this issue will be greatly appreciated. Thanks in advance for your help. 

MarkSnodgrass

@Sgoedecke 

Have you tried just evaulating based on the month of the period instead of the entire date and just look for January? It would look like this:

SUM(SUM((CASE 
WHEN MONTH(`PERIOD`) = 1 THEN (`Beg Balance`+`AMOUNT`) ELSE `AMOUNT` END)
       )
   ) 

   OVER(ORDER BY `PERIOD`)

It seems like using the MONTH function would make it dynamic for each year

Sgoedecke

@MarkSnodgrass  This would work if there is only 1 January in the date range. However, if there are multiple January's it will break at the second January. The over fucntion is adding each subsequent row together and the Month(`Period`) function will bring in the beginning balance in my original example from January 2017, which would give an overstated balance going forward. Make sense?