Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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?

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors