Next 3 month Average

Question

PBIX link:https://drive.google.com/file/d/1UKVVjEndFDuBUnsOAGVbg_CnweY2cR-d/view?usp=sharing&nbsp;In the file attached, I want to get the average of next 3 months Forecasted payments(excluding current month)&nbsp;&nbsp;&nbsp;I understand a formula like this will work:Next 3 month average = CALCULATE (AVERAGE(‘To Collect’[To Collect] ),DATESINPERIOD ( ‘Date’[Date], EOMONTH(TODAY(),0), 3, MONTH ))&nbsp;But , in the current example Forecasted Payments formula is bit complicated, so I don’t know how to make it work in this case.

mahoneypat · Answer

Please try this measure expression to get the 3 month average.
&nbsp;
FP&nbsp;3&nbsp;Month&nbsp;=VAR&nbsp;maxdate&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;'Date'[Date]&nbsp;)VAR&nbsp;startnextmonth&nbsp;=&nbsp;maxdate&nbsp;+&nbsp;1VAR&nbsp;end3month&nbsp;=&nbsp;&nbsp;&nbsp;&nbsp;EOMONTH&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;startnextmonth,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;)RETURN&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AVERAGEX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DISTINCT&nbsp;(&nbsp;'Date'[MonthInCalendar]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Incremental&nbsp;POC],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;-&nbsp;[LastMonthPOC]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;*&nbsp;474&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;'Date'[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Date'[Date]&nbsp;&gt;=&nbsp;startnextmonth&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;'Date'[Date]&nbsp;&lt;=&nbsp;end3month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
Regards,
Pat

Forum Discussion

Next 3 month Average

1 Reply

Related content

Calculating average 12 month average

Total per month / average per month

Dynamic Average per month Calculation

Average for Month Excluding Current Month

12 Month Rolling average.