Advanced period formulas
An overview of advanced period and date formulas. Understand the different building blocks, how to construct your own formulas, and get practical examples for both period- and date-based needs. Also provides an overview of the built-in period functions in Report Designer.
Period vs. date - what is the difference?
A period is an entire month (202201 = January 2022). A date is a specific day (01.01.2022). When a field requires day precision - like due date - you use date-formula . If you don’t know the parameter name, you can find it via the magnifying glass on your filter, which takes you to the lookup window. Here the name is between the curly brackets. You will also find it on the Excel ribbon OneStop Reporting when you go to the “report parameter” dialog box.
Structure of formulas
All formulas follow the same pattern: You start with a period or a date, and add commands that move or expand the period interval. Understand these building blocks, and you can read and put together the formulas you need. A formula must always start with a left-facing curly bracket {, and end with a right-facing curly bracket }.
Rule of thumb: Period-formulas (month, e.g. 202201) do not have apostrophes around them. Date-formulas (with .SqlDate) must have apostrophes: ‘{…}’.
Periods and dates have some different building blocks and are therefore split into two different tables below. Note that for both formula types it is possible to use static values for periods or dates if you do not wish to base the calculation on a dynamic parameter.
How a period formula is structured
Used when the dimension is a period. Replace Parameter with your parameter name.
| Building block | What it does |
|---|---|
| PeriodCalc.GetPeriod(Parameter) | The starting point that retrieves the period for which the report is run. Parameter is replaced with the name of your period parameter (e.g. Accounting periods). |
| .AddYears(X) | Moves X years forward (positive number) or backward (negative number). .AddYears(-1) = last year. |
| .Add(X) | Moves X months/periods. Add(-1) = last month. |
| .QuarterStart / .QuarterEnd | Finds the quarter’s start or end based on specified period |
| .WholeYear | Expands to cover the whole year from January to December. |
| .YearStart / .YearEnd | Jumps to first / last period of the year. |
| .Extend(X) | Expands an interval by X months beyond the start point in the formula. Extend(2) after a start month gives 3 months total = one quarter. |
| .FiscYearToDate | Retrieves year to date based on the period parameter |
| .LastYearToDate | Retrieves last year to date based on the period parameter |
| .SetIndex(X) | Sets the period to month no. X in the year (1 = January, 4 = April etc.). Used for example to hit a specific quarter. |
| : (colon) | Creates an interval between two expressions: {start}:{end}. |
Examples of period-based formulas
| Description | Formula |
|---|---|
| Whole year this year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).WholeYear} |
| First period in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart} |
| Last period in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).YearEnd} |
| Q1 in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart.Extend(2)} |
| Q2-Q4 in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).YearStart.SetIndex(X).Extend(2)} |
| Remaining periods in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X).Add(1)}:{PeriodCalc.GetPeriod(Parameter).AddYears(X).YearEnd} |
| This period in year X | {PeriodCalc.GetPeriod(Parameter).AddYears(X)} |
| Current period | {PeriodCalc.CurrentPeriod} |
| Rolling from X to Y | {PeriodCalc.GetPeriod(Parameter).Add(X).Extend(Y)} |
You want to show 2 years rolling backward. Then you must first subtract 1 month and expand (Extend) by 23 months. Here X will get the value -1 and Y gets the value -23. The formula will look like this: {PeriodCalc.GetPeriod(AccountingPeriods).Add(-1).Extend(-23)}
How a date-based formula is structured
Used when the dimension is a date (voucher date, due date, transaction date) rather than an accounting period. Replace DateParameter with the name of your date parameter. These must have single quotes and .SqlDate.
| Building block | What it does |
|---|---|
| PeriodCalc.Now | Uses today’s date based on server clock which is UTC. Used when you want to calculate from “today” instead of a selected period. |
| .AddYears(X) | Moves X years forward (positive number) or backward (negative number). .AddYears(-1) = last year. |
| .AddMonths(X) | Moves X months/periods. AddMonths(-1) = last month. |
| .AddDays(X) | Moves X days. |
| .YearStart / .YearEnd | Jumps to first / last period of the year. |
| .WeekStart | Jumps to the start of the week (Monday in most setups - see note under week-formulas). |
| .MonthStart / .MonthEnd | Jumps to first / last day of the month. |
| .SqlDate | Format the result as a date (yyyy-mm-dd). Must be included on all date-based formulas. |
| : (colon) | Creates an interval between two expressions: {start}:{end}. |
Examples of date-based formulas
DateParameter = name of period parameter. Parameter names work with and without @ sign. Remember to start and end the formula with apostrophe.
| Description | Formula |
|---|---|
| Today: | ‘{PeriodCalc.Now.SqlDate}’ |
| Move X years: | ‘{DateParameter.AddYears(X).SqlDate}’ |
| Move X months: | ‘{DateParameter.AddMonths(X).SqlDate}’ |
| Move X days: | ‘{DateParameter.AddDays(X).SqlDate}’ |
| Yesterday last year: | ‘{DateParameter.AddYears(-1).AddDays(-1).SqlDate}’ |
| This week’s days (from week start to today): | ‘{PeriodCalc.Now.WeekStart.SqlDate} : {PeriodCalc.Now.SqlDate}’ |
| To month end: | ‘{DateParameter.MonthEnd.SqlDate}’ |
| From month start: | ‘{DateParameter.MonthStart.SqlDate}’ |
| To/from year end and year start: | ‘{DateParameter.YearEnd.SqlDate}’ |
| Due within 30 days: | ‘{DateParameter.AddDays(30).SqlDate}’ |
| Today-Sunday in same week. Retrieves the rest of the week | ‘{PeriodCalc.Now.SqlDate}: {PeriodCalc.Now.WeekStart.AddDays(6).SqlDate}’ |
| Monday-Sunday (entire current week) | ‘{PeriodCalc.Now.WeekStart.SqlDate}: {PeriodCalc.Now.WeekStart.AddDays(6).SqlDate}’ |
You want to show next year start to last day of 2026 with a static date: ‘{DateParameter.YearEnd.AddDays(1).SqlDate}:2026-12-31’
Check which day the week starts on
Depending on your ERP system, week start can be calculated differently, where some are calculated in SQL and others are retrieved from calendar in ERP. Therefore Sunday will be calculated as week start for some, and others Monday. Run the report on a known date and confirm that start/end date lands on the correct day for your reporting. If it hits wrong, adjust the number in AddDays(..) accordingly.
Overview of built-in period functions in Report Designer
In the table below you will find an overview of the period functions in ReportDesigner. You can work further with these formulas and adapt them to your needs if you need other period views. If you use the formula in a filter, fill in the name of the period parameter between the brackets [ ]. The @ sign before parameter name does not need to be included, but it works with and without.
| Description | Formula |
|---|---|
| Year and periods | |
| Whole current year | {PeriodCalc.GetPeriod([]).WholeYear} |
| Whole last year | {PeriodCalc.GetPeriod([]).AddYears(-1).WholeYear} |
| Whole next year | {PeriodCalc.GetPeriod([]).AddYears(1).WholeYear} |
| Whole year, 2 years back | {PeriodCalc.GetPeriod([]).AddYears(-2).WholeYear} |
| Whole year, 3 years back | {PeriodCalc.GetPeriod([]).AddYears(-3).WholeYear} |
| First period this year | {PeriodCalc.GetPeriod([]).YearStart} |
| First period next year | {PeriodCalc.GetPeriod([]).AddYears(1).YearStart} |
| Last period this year | {PeriodCalc.GetPeriod([]).YearEnd} |
| First period last year | {PeriodCalc.GetPeriod([]).AddYears(-1).YearStart} |
| Last period last year | {PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd} |
| Last period next year | {PeriodCalc.GetPeriod([]).AddYears(1).YearEnd} |
| Previous period | {PeriodCalc.GetPeriod([]).Add(-1).Extend(0)} |
| The period this year | {PeriodCalc.GetPeriod([])} |
| Period last year | {PeriodCalc.GetPeriod([]).AddYears(-1)} |
| This period 2 years ago | {PeriodCalc.GetPeriod([]).AddYears(-2)} |
| This period 3 years ago | {PeriodCalc.GetPeriod([]).AddYears(-3)} |
| This period next year | {PeriodCalc.GetPeriod([]).AddYears(1)} |
| Year to date (YTD) | {PeriodCalc.FiscYearToDate([])} |
| Last year to date (LYTD) | {PeriodCalc.LastYearToDate([])} |
| All periods to date | {PeriodCalc.GetPeriod([0])}:{PeriodCalc.GetPeriod([])} |
| All periods to last period last year | {PeriodCalc.GetPeriod([0])}:{PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd} |
| Remaining periods this year | {PeriodCalc.GetPeriod([]).Add(1)}: {PeriodCalc.GetPeriod([]).YearEnd} |
| Remaining periods last year | {PeriodCalc.GetPeriod([]).AddYears(-1).Add(1)}: {PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd} |
| Quarters | |
| Q1 this year | {PeriodCalc.GetPeriod([]).YearStart.Extend(2)} |
| Q1 last year | {PeriodCalc.GetPeriod([]).AddYears(-1).YearStart.Extend(2)} |
| Q2 this year | {PeriodCalc.GetPeriod([]).SetIndex(4).Extend(2)} |
| Q2 last year | {PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(4).Extend(2)} |
| Q3 this year | {PeriodCalc.GetPeriod([]).SetIndex(7).Extend(2)} |
| Q3 last year | {PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(7).Extend(2)} |
| Q4 this year | {PeriodCalc.GetPeriod([]).SetIndex(10)}:{PeriodCalc.GetPeriod([]).YearEnd} |
| Q4 last year | {PeriodCalc.GetPeriod([]).AddYears(-1).SetIndex(10)}:{PeriodCalc.GetPeriod([]).AddYears(-1).YearEnd} |
| This quarter | {PeriodCalc.QuarterStart([])}:{PeriodCalc.QuarterEnd([])} |
| This quarter last year | {PeriodCalc.QuarterStart([]).AddYears(-1)}:{PeriodCalc.QuarterEnd([]).AddYears(-1)} |
| Previous quarter | {PeriodCalc.QuarterStart([]).Add(-3)}:{PeriodCalc.QuarterEnd([]).Add(-3)} |
| Previous quarter last year | {PeriodCalc.QuarterStart([]).AddYears(-1).Add(-3)}:{PeriodCalc.QuarterEnd([]).AddYears(-1).Add(-3)} |
| Next quarter | {PeriodCalc.QuarterStart([]).Add(3)}:{PeriodCalc.QuarterEnd([]).Add(3)} |
| Next quarter last year | {PeriodCalc.QuarterStart([]).AddYears(-1).Add(3)}:{PeriodCalc.QuarterEnd([]).AddYears(-1).Add(3)} |
| Quarter to date | {PeriodCalc.QuarterStart([])}:{PeriodCalc.GetPeriod([])} |
| Quarter to date last year | {PeriodCalc.QuarterStart([]).AddYears(-1)}:{PeriodCalc.GetPeriod([]).AddYears(-1)} |
| Rolling periods | |
| 3 months rolling (-1 to - 3) | {PeriodCalc.GetPeriod([]).Add(-1).Extend(-2)} |
| 3 months rolling (next 3) | {PeriodCalc.GetPeriod([]).Add(1).Extend(2)} |
| 3 months rolling (+4 to +6) | {PeriodCalc.GetPeriod([]).Add(4).Extend(2)} |
| 3 months rolling (+7 to +9) | {PeriodCalc.GetPeriod([]).Add(7).Extend(2)} |
| 3 months rolling (+10 to +12) | {PeriodCalc.GetPeriod([]).Add(10).Extend(2)} |
| 12 months rolling, backward (last month and 11 before) | {PeriodCalc.GetPeriod([]).Add(-1).Extend(-11)} |
| 12 months rolling, backward, incl. current | {PeriodCalc.GetPeriod([]).Add(0).Extend(-11)} |
| 12 months rolling, forward | {PeriodCalc.GetPeriod([]).Add(1).Extend(11)} |
| 12 months rolling forward, incl. current | {PeriodCalc.GetPeriod([]).Add(0).Extend(11)} |
| 24 months rolling (this period to +23) | {PeriodCalc.GetPeriod([]).Add(0).Extend(23)} |
| 24 months rolling, backward, (this period to -23) | {PeriodCalc.GetPeriod([]).Add(0).Extend(-23)} |
How to describe a new need
If you need a formula not listed here, provide a description with the information below, and you will get a proposal back from Gaia. The more concrete the information is, the better the formula hits the first time.
- Period or date? Are you filtering on an accounting period (whole month) or a date (specific day, like due date)? This determines the entire setup.
- What is the starting point? Should the formula calculate based on the period the report is run for, or from today’s date?
- Single period or interval? Do you want one period/day, or a range from-to (like “the last 12 months”)?
- Which offset? Describe the movement in plain text - “last quarter”, “same month last year”, “from Monday to today”. Feel free to include the name of your parameter if you know it.
Example of a good order
“Date-based, calculate from today, I want an interval from first day this month up to today’s date. The parameter is named VoucherDate.”