Blogs / SAP / SAP BusinessObjects / SAP Lumira Discovery / YTD, QTD, MTD Calculations in SAP Lumira Discovery

# YTD, QTD, MTD Calculations in SAP Lumira Discovery

Mar 2, 2018

This is part of the SAP Lumira Discovery blog series.

According to Wikipedia, Year-to-date (YTD) is a period, starting from the beginning of the current year (either the calendar year or fiscal year) and continuing up to the present day. YTD is used in many contexts, mainly for recording results of an activity in the time between a date (exclusive, since this day may not yet be “complete”) and the beginning of the year.

In SAP Lumira Discovery, we would need to leverage the Calculations function to build a custom dimension for this purpose. The basic requirements for performing this calculation is,

– A date field against which you would want to compare
– A measure that needs to be basis for this calculation
– Year() function

– CurrentDate() function

– if-then-else statement

We can then calculate the YTD custom dimension using the formula below,

We can then extend the same concept to building QTD (Quarter-to-Date) and MTD (Month-to-Date) calculations as shown below,

`YTD:if Year({Date})=Year(CurrentDate()) and {Date} <=(CurrentDate()) then {Sales} else 0QTD:if Quarter({Date}) = Quarter(CurrentDate()) and {Date} <= (CurrentDate()) then {Sales} else 0MTD:if Month({Date}) = Month(CurrentDate()) and {Date} <= (CurrentDate()) then {Sales} else 0`

This can be even extended to previous duration values as shown below,

`PYTD:if Year({Date}) = Year(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0PQTD:if Quarter({Date}) = Quarter(CurrentDate())-1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0PMTD:if Month({Date}) = Month(CurrentDate()) - 1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0`

An important approach would be to create a custom dimension first and then create a measure. This process would work only for imported data, as these measures can be directly inherited from SAP sources via a live connection.

Get your business users trained on more best practices with calculations in SAP Lumira Discovery.

Want to know more? Click here to get in touch.

Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com