Dynamic Date Field with Over Function and Balance Forward Question

Sgoedecke
Sgoedecke
in Magic ETL

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. 

Categories