YTD Average in Story Calculation Editor

[raviv7]

Hi All,

While calculating YTD average, I would like to get values like below,

YTD Average = (All Previous Month YTD Sales Values + Current Month YTD Sales Values )/Current Month Number

For Example,

Jan = 100(Jan)/1

Feb = 100+300(Jan+Feb)= 400/2 = 200

March = 100+300+600(Jan+Feb+Mar)= 1000/3= 333.33

Do you have any idea how to achieve this scenario in Story calculation editor ?

nikhil_1486

Best Regards,

Ravi

[N1kh1l]

raviv7

There are 2 solutions. Please choose whatever is feasible for you.

Solution 1:

Create calculated measure to get YTD periods

IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jan"  ,1 , IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Feb"   ,2, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Mar"  ,3, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Apr"  ,4, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="May"  ,5, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jun"  ,6, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jul"  ,7, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Aug"  ,8, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Sep"  ,9, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Oct"  ,10, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Nov"  ,11, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Dec"  ,12))))))))))))    

Create Restricted Measure to get YTD Sales as below

Create a Table formula to get running sum of YTD Sales. This was the challenge as Time shifting (restriction ) is not supported on measure which is already restricted by time (YTD Sales). It will create a running Sum row which can be renamed to Accumulative YTD Sales.

Now another challenge is the above table calculation is not available for use in calculation editor so we will use another table formula to calculate the final YTD average.

Select both Accumulative YTD Sales and Periods by holding the control key and right click to get the calculation as below. It will create a row called Divide which you can rename it to YTD Sales Average.

Output:

Solution 2:

Please note this only got supported after the recent tenant update.

Create calculated measure in story to get YTD periods

IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jan"  ,1 , IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Feb"   ,2, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Mar"  ,3, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Apr"  ,4, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="May"  ,5, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jun"  ,6, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Jul"  ,7, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Aug"  ,8, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Sep"  ,9, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Oct"  ,10, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Nov"  ,11, IF([d/"NA_SAP_CEP_SALES_PLANNING":Date].[p/MONTHDESC]="Dec"  ,12))))))))))))    

Create calculated member in Account Dimension/Measure

1. YTD_Sales: YTD Sales

LOOKUP([SALES] ,[d/Date]=LastPeriods( "Month",12))

2. Cumm_YTD_Sales : Cumulative YTD Sales

ITERATE(PRIOR()+[YTD_Sales] ,[YTD_Sales] ,[d/Date].[p/CALMONTH] )

Please Note: ITERATE() and PRIOR() are latest updates in SAC and might even come to calculation editor some day.

Now we have all the calculations required to create YTD Average Sales calculated measure in the story

YTD Avg Sales

["NA_SAP_CEP_SALES_PLANNING":Cumm_YTD_Sales]/[#Periods]

Output:

Please upvote/accept if this helps.

Nikhil

[raviv7]

Hi nikhil_1486

nikhil_1486Thanks.

I have tried the second solution in Key figure based model (Measure Based model). Both formula YTD_SALES and YTD_Period works fine.

Here YTD_period is shows correct values. For 2022 YTD_SALES, it's considering the 2020 and 2021 data due to the formula structure. Can you let me know how to restrict this formula only to 2022.

LOOKUP([SALES] ,[d/Date]=LastPeriods( "Month",12))

2020

2022

As you mentioned ITERATE() and PRIOR() these two are not available in my Version. Can you mention the SAC version where it's available ?

Best Regards,Ravi