Dynamic Date Field with Over Function and Balance Forward Question
Please see the attached video to better explain what we are trying to accomplish.
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
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 6 Cloud Amplifier
- 10 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 780 Automate
- 191 Apps
- 482 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 41 AI and Machine Learning
- 21 AI Chat
- 2 AI Projects and Models
- 18 Jupyter Workspaces
- 414 Distribute
- 122 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 146 Manage
- 142 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive