3 Quarter/Year Average Obligation Projection

[heathqm]

I am trying to calculate the next fiscal year projection of obligations by using the past three years average broken up by quarter. So if the year is 2020 I want quarter 1 to be the average of (2019 Q1 + 2018 Q1 + 2017 Q1) and so on for Q2, Q3 and Q4. 

The variables are Fiscal Quarter (QQ) and Fiscal Year(YYYY) or else a variable Fiscal Year/Quarter (YY/YY-QQ) as dimensions and measure would be total obligation.

something like this: 

FY17 Q1 Actual  Total Obligations	FY17 Q2  Actual Total  Obligations
$      466,376,563.88	$    180,019,412.11
FY18 Q1 Actual  Total Obligations	FY18 Q2  Actual Total  Obligations
$      399,531,083.74	$    346,144,035.52
FY19 Q1 Actual  Total Obligations	FY19 Q2  Actual Total  Obligations
$527,515,076.92	$285,722,666.19
FY20 Q1 Projection  Total Obligations	FY19 Q2  Projection Total  Obligations
$464,474,241.51	$270,628,704.61

[Kushal_Chawda]

@heathqm  Let's say you have 5 years data from 2015 to 2020. So for 2017 Q1 what should be the value because you will have only two years quarters (2016 Q1 & 2015 Q1).

[heathqm]

Right now I am only going to be using it to go forward for projections of 2021 and on but, to answer the questions it would just be the two quarters you would use and it would fill the other with 0. I don't think that is relevant. 

[Kushal_Chawda]

@heathqm  You need to create fiscal quarter field with numeric value associated with it to sort the fiscal quarter which will be useful to work above function as expected. You can create it as below in your load script. Assuming you have Date field in your data to create the fiscal quarter.

dual('FY'&date(YearStart(Date,0,4),'YY')& ' Q'&ceil((Mod(Month(Date) - 4, 12)+1)/3),QuarterStart(Date,0,4)) as Fisacal_Quarter

Now on frond end create a chart with dimension Fisacal_Quarter and below expression. Assuming Sum(Obligation)  is your measure you can change your measure as per your need

=sum(aggr(RangeAvg(above(sum({<Fisacal_Quarter>}Obligation),4),above(sum({<Fisacal_Quarter>}Obligation),8),above(sum({<Fisacal_Quarter>}Obligation),12)),Fisacal_Quarter))*avg(1)

[heathqm]

Unfortunately that did not work. It just gives a sum of 0. 

[Kushal_Chawda]

@heathqm  If you are doing it the same way I have described with your actual expression it should work. Can you share what you have done and not working?

[heathqm]

I added 

Dual('FY'&date(YearStart(Date,0,4),'YY')& ' Q'&ceil((Mod(Month(Date) - 4, 12)+1)/3),QuarterStart(Date,0,4)) as [Fiscal_Quarter],

into my autocalendar called Fisc which is a fiscal year auto calendar. Then I added  DERIVE FIELDS FROM FIELDS
[Effective Date] using [FISC]; Then I loaded the data in the data load editor which worked fine. 

Then I went to create a measure like this: 

sum(aggr(RangeAvg(above(sum({<[Effective Date.Fisc.Fiscal_Quarter]>}[ Obligation ($)]),4),above(sum({<[ Effective Date.Fisc.Fiscal_Quarter]>}[ Action Obligation ($)]),8),above(sum({<[ Effective Date.Fisc.Fiscal_Quarter]>}[ Obligation ($)]),12)),[Effective Date.Fisc.Fiscal_Quarter]))*avg(1)

I also tried to add 

Dual('FY'&date(YearStart(Date,0,4),'YY')& ' Q'&ceil((Mod(Month(Date) - 4, 12)+1)/3),QuarterStart(Date,0,4)) as [Fiscal_Quarter],

in a separate part in the data load editor that was not the auto calendar and still received 0 as an answer.  

[Kushal_Chawda]

@heathqm  Where you are using this measure? Can you try creating table with Dimension Fiscal_quarter and then use that measure.

Also you can try creating table with Dimension Fiscal_Quarter and simple measure  sum([ Obligation ($)]) and see if you are getting values or not. That way it will be sure that Your fiscal_quarter is linked to your actual data

[heathqm]

I tried creating a table and am getting 0 for value so the fiscal_quarter must not be liked to the actual data. Hmm not sure why not.

[Kushal_Chawda]

@heathqm  I would suggest to use custom calendar script and link the calendar date with actual data date. then it should work