Calculation of Financial YTD(starting from April till Marc) of a 3 month moving average of sales

[Msk_2080]

I have calculated the last 3 months moving average of sales using the below calculation. 

3_month_moving_avg =

Var enddate = MAX(Sales_forescat[reporting_month])-1

var startdate = EDATE(enddate,-3)+1

var result =

CALCULATE(AVERAGE(Sales_forescat[Rolling_sales]),

DATESBETWEEN(Sales_forescat[reporting_month], startdate, enddate))

return

result

My intention now is to calculate the Financial YTD average of this 3_month_moving_avg . The calculation i am using is as below

YTD = CALCULATE([3_month_moving_avg],DATESYTD(Sales_forescat[reporting_month],"31/3"))

But the value is same as the value of 3_month_moving_avg. Please help me

reporting_month	Rolling_sales	3 month avg	YTD rolling	Month	YTD_Avg
4/1/2016 0:00	41530	39695	39695	1	39695
5/1/2016 0:00	56863	40307	80002	2	40001
6/1/2016 0:00	70280	46029	126031	3	42010
7/1/2016 0:00	81484	56224	182255	4	45564
8/1/2016 0:00	96692	69542	251797	5	50359
9/1/2016 0:00	111264	82819	334616	6	55769
10/1/2016 0:00	127914	96480	431096	7	61585
11/1/2016 0:00	142995	111957	543053	8	67882
12/1/2016 0:00	158568	127391	670444	9	74494
1/1/2017 0:00	172614	143159	813603	10	81360
2/1/2017 0:00	187547	158059	971662	11	88333
3/1/2017 0:00	201112	172910	1144572	12	95381

[v-yalanwu-msft · Accepted solution]

Hi, @Msk_2080 ;

Please try it.

YTD = AVERAGEX(DATESYTD(Sales_forescat[reporting_month],"31/3"),[3_month_moving_avg])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Usually when you calculate moving averages you do so based on the absolute values (monthly sales) , not the cumulative values.  It sounds like you want to take the average of the average of the cumulative values? Can you please confirm?