- SAP Managed Tags:
After having come across this question from both partners and customers few times, I am putting down a blog on how to achieve the following usecase: As a user I would like to display total sales for the Current Month and Previous Year same Month.
In SAP Analytics Cloud you can achieve this by creating restricted measures for your sales measure. I will show you how to proceed with that.
In my dataset, I have Total sales measure, which is what I will restrict using the time dimension "Order date" that I already have in my dataset.
Step 1: In your designer panel for the selected crosstab/table you need to add a new calculation.
Step 2: Create the restricted measure calculation by restricting "Total Sales" measure for the Current Month as follows
Once selected, you would be displayed with the dynamic range (based on today's date, 2nd Dec 2019) selected as follows
Click ok to create the Restricted Measure.
Similarly, we will create another restricted Total Sales based measure, but this time we would restrict that to previous year same month.
Step 3: In your designer panel for the selected crosstab/table you need to add a new calculation. And this time define the restrictions as follows
Once selected, you would be displayed with the dynamic range (based on today's date, 2nd Dec 2019) selected as follows.
Step 4: The range in the above step is for previous month and not the current month of the previous year. Hence, we need to manage the offset of the month, and this we can do by cliking the drop-down icon highlighted in blue in the image above.
Define the offset as follows and we will have desired month (PY same month)
Click ok to create the Calculation.
To verify if your calculations are behaving correctly, you can add them to a crosstab with time dimension included and the drill down to months of current and previous years to validate the values as given below.
Based onthe approach defined in this blog, you can arrive at all the following calculations
Similarly to Date calculations can also be done like
and even more ..
In SAP Analytics Cloud you can achieve this by creating restricted measures for your sales measure. I will show you how to proceed with that.
Current Month Sales
In my dataset, I have Total sales measure, which is what I will restrict using the time dimension "Order date" that I already have in my dataset.
Step 1: In your designer panel for the selected crosstab/table you need to add a new calculation.
Step 2: Create the restricted measure calculation by restricting "Total Sales" measure for the Current Month as follows
Once selected, you would be displayed with the dynamic range (based on today's date, 2nd Dec 2019) selected as follows
Click ok to create the Restricted Measure.
Previous Year Same Month Sales
Similarly, we will create another restricted Total Sales based measure, but this time we would restrict that to previous year same month.
Step 3: In your designer panel for the selected crosstab/table you need to add a new calculation. And this time define the restrictions as follows
Once selected, you would be displayed with the dynamic range (based on today's date, 2nd Dec 2019) selected as follows.
Step 4: The range in the above step is for previous month and not the current month of the previous year. Hence, we need to manage the offset of the month, and this we can do by cliking the drop-down icon highlighted in blue in the image above.
Define the offset as follows and we will have desired month (PY same month)
Click ok to create the Calculation.
Verifying the Calculations
To verify if your calculations are behaving correctly, you can add them to a crosstab with time dimension included and the drill down to months of current and previous years to validate the values as given below.
Based onthe approach defined in this blog, you can arrive at all the following calculations
- Current Month (CM)
- Current Quarter (CQ)
- Current Year (CY)
- Previous Month (PM)
- Previous Quarter (PQ)
- Previous Year (PY)
- Previous Year Same Month (PYSM)
- Previous Year Same Quarter (PYSQ)
Similarly to Date calculations can also be done like
- Month to Date (MTD)
- Quarter to Date (QTD)
- Year to Date (YTD)
and even more ..
2 Comments
