Overview

Cosmos comes with a wide variety of date intelligence through our built-in Date table, which assists users with adding in things like Month-to-Date (MTD) or Year-to-Date (YTD) calculations. This article covers how to add these types of calculations to your report.

Details

When building in calculations for things like Year-to-Date (YTD), there are a few simple steps to take to ensure that everything is set up. 

First, you will want to make sure that you have a Report Option added so that you can specify the date range that you want to list out in your report. In the example below, we have a Report Option added to return all months between 1/1/2022 and 10/31/2022.

Next, you will want to create a Range List (in this example a columns Range List) that will list out all the month-end dates in the date range that was specified in the Report Option. To do this, highlight the range of cells that you want included in the range and then navigate to Range Lists -> Add Range List and select the Last Day of Month field from the Date table.

You can see in the screenshot below that I have added this columns Range List that covers C1:C7 and there is a function in cell C2 that will return the Last Day of Month field. 

When the report is run it will generate a list of dates representing the end of each month covered in my Report Option filter.

Next, since each column will represent a cumulative YTD total, we will add a second columns Range List to bring in the First Day of Year field from the Date table. The range that this columns Range List covers will be the same range as our previous Range List (C1:C7). To add this, you will highlight the same range as the other range list and navigate to Range Lists -> Add Range List and then add the First Day of Year field.

You will now see an additional date above the original one that will represent the starting date of the year in cell C1.

You can see that the report now contains the correct date range needed to calculate the YTD value when the report is run:

The last steps will be to add in the rest of the Cosmos functions needed to finish the report. In this example we added a customer list to return all customer numbers and names. In the column that contains the dates we will then add a Cosmos Field Function to sum up the sales amounts.

One important step in this process is to ensure that the filters for the dates are set correctly in this sum Cosmos function. Since the goal is to return the YTD numbers for each customer you will want to make sure that there is a filter for Posting Date (or whichever date field you want to use) where the operator is set to BETWEEN and the start and end filters are set to use the First Day of Year and Last Day of Month range lists.

This will ensure that the summing of transactions is done on the correct date range to give you the YTD values.