Custom Month and Year to Date to Last Year MTD and QTD Calculations

[Community_Admin]

Month to Date (MTD) and Year to Date (YTD) calculations provide us results of activity in the time between the beginning of a date (can be a month or year in our example) and the beginning of the current month or year. This in turn allows data analysts, managers, and other stakeholders to track performance against specific time periods.

With Sisense you can calculate MTD, QTD (Quarter to Date), and YTD periods by creating custom fields in the ElastiCube Manager. In this post we'll focus on custom calculations to compare MTD to Last Year MTD and YTD to Last Year YTD. 

Custom MTD to Last Year MTD

1. Create an indicator in the table that contains the date field and measure you want to track.  This is done by adding a custom field in the ElastiCube Manager. 

CASE

WHEN date <= now()

AND getmonth(date) =getmonth(now())

AND getyear(date) = getyear(now()) THEN 'MTD'

WHEN getyear(date) = getyear(now())-1

AND getmonth(date) = getmonth(now())

AND getday(date) <= getday(now()) THEN 'Last Year MTD'

ELSE null

end

2. Create a custom measure in the dashboard that calculates your MTD and Last Year MTD scoped measures.  The section before the comma is the calculation.  The section after the comma is the scoping based on the new field created in the ElastiCube.  This can be scoped for either MTD or Last Year MTD.

*Tip: Once both the MTD and Last Year MTD measures are created a third measure can be created to compare the two either by percentage or true values.  This will give performance measures against similar time periods from the previous month.

Custom YTD to Last Year YTD

1. Create an indicator in the table that contains the date field and measure you want to track.  This is done by adding a custom field in the ElastiCube Manager.

CASE

WHEN getyear(date) =getyear(now())

AND date <= now() THEN 'YTD'

WHEN getyear(date) = getyear(now())-1

AND date <= adddays(now(),-365) THEN 'Last YTD'

ELSE null

end

2. Create a custom measure in the dashboard that calculates your YTD and Last YTD scoped measures.  The section before the comma is the calculation.  The section after the comma is the scoping based on the new field created in the ElastiCube.  This can be scoped for either YTD or Last YTD.

*Tip: Once both the YTD and Last YTD measures are created a third measure can be created to compare the two either by percentage or true values.  This will give performance measures against similar time periods from the previous month.