ToDate Model Calculations

By Adam Arends
02/27/2024

Table of Contents

Introduction to To-Date Formulas in SAC

In this SAC release, additional model-level calculations have been made available. If your reporting requirements include comparing to-date across dimensions or versions, this additional calculation will be beneficial. In this post, we will explore the three new To-Date Model-level Measure Calculations and how they can be used to enhance reporting.

ToDate and ToPeriod in SAC

SAC now offers MTD, QTD, and YTD calculations when creating a Calculated Measure. Before the Q4 2023 release, to-date measures were more easily managed in Story Calculations utilizing dynamic date filtering. Being tied to a story, the calculations would have to be re-developed in each additional story.

In the QRC4 Release last year, SAC offered the ToPeriod calculation that could modify the Date dimension inside a Lookup of Restrict function, allowing a To-Date view to be created. These new calculations are an extension of that function and use the same underlying logic under a new syntax.

Becomes:

As you can see from the modified syntax, the QTD function essentially consolidated the =ToPeriod(“Quarter”) to the backend of the function with the same output. This new syntax expedites the calculation process while providing additional clarity to the intent of the calculation itself (and not affecting previously implemented ToPeriod calculations)

In the action, the results look like this:

As you can see, the To-Date calculations display the cumulative sum of the periods to the next level date level (in a daily model, the formula will apply up to the date filtered for Month-to-Date). Below, you can see how it can be used for a To-Date comparison of actuals to budget.

Create To-Date Calculated Measures

In the Modeler, Switch to Calculation Management:

Add a calculated measure and give it a name and description (note the change in the layout of the Modeler Screens).

In the Properties Panel on the right of the screen, enter the required formula (MTD, QTD, or YTD). The formula will complete the [d/Date] dimension since it is required. Provide the formula with the Measure to calculate against. To open the Formula Editor, select the icon at the top-right of the box.

These new Formulas work in the New Model and the Classic Model. In the Classic Model, the Account is used instead of the Measure.

With these new To-Date enhanced formulas, you can now implement comparative reporting and visuals in stories without complex calculations or repetitive story development.

Related Posts

Quarterly Release Cycle

Aug 21 2024

Table: Support of Advanced Filter With Unbooked Mode

Feature Overview In the recent SAC QRC3 update, tables were updated to support advanced filters with Unbooked Mode. Advanced filtering allows users to filter

Kyle Sanborn

Quarterly Release Cycle

Aug 21 2024

Schedule and Deliver Story Content within the Email Body

Feature Overview The Schedule Publication feature now allows users to include a preview in the email delivery option. You will be able to choose

Crystal Vazquez

Quarterly Release Cycle

Aug 21 2024

Show Last Comment in Comment Widget

Feature Overview The QRC3 of 2024 introduces a new update for the comment widget. Now, users have the option to show only the last

Crystal Vazquez

New SAP Boosters!

Learn all about SAP functions in the SAC Add-in for Excel or discover the all new SAC Add-in for PowerPoint.

Contact Us

Reach out if you have any questions or to see how we can create a custom training solution for your organization!

Contact Form Pop-Up - General Use

Custom Training Solution

Fill out and submit the form below, and we’ll reach out to you to discuss how we can work toward achieving your training goals in a way that works best for you and your team.

Contact Form - Custom Training