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
 
YTD, QTD, MTD Calculations in SAP Lumira Discovery - 1
 
– CurrentDate() function
 
YTD, QTD, MTD Calculations in SAP Lumira Discovery - 2
 
– if-then-else statement
 
YTD, QTD, MTD Calculations in SAP Lumira Discovery - 3
 
We can then calculate the YTD custom dimension using the formula below,
 
YTD, QTD, MTD Calculations in SAP Lumira Discovery - 4
 
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 0
QTD:
if Quarter({Date}) = Quarter(CurrentDate()) and {Date} <= (CurrentDate()) then {Sales} else 0
MTD:
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 0
PQTD:
if Quarter({Date}) = Quarter(CurrentDate())-1 and DayOfYear({Date}) <= DayOfYear(CurrentDate()) then {Sales} else 0
PMTD:
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.

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com