Current Month Calc, forecast and Pro rate

Hi Cherie,

You may get a better view from the PBIX file - hopefully you can see it.

Avg Forecast Rpt

I have broken the calculations down and put them in the matrix along with what would be the final field which presents correctly but the calcualtion is not the average of the previous 3 mths.

Thanks Binway

I found a more elegant and accurate way to calculate the average for the previous 3 months.

Average_Test = AVERAGEX( DATESBETWEEN('Date'[Date],
                       DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
        DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) 
          ) ,
        CALCULATE(SUMX(Sales,DIVIDE(Sales[YTD less rebates],Sales[DistnctSesnlDys],0))/1000,FILTER(ALL(Sales[Currency]), Sales[Currency]="AUD"))
                                )

I am now looking to filter and add the pro-rate calcualtion for the current month as well as Forecast the value to the end of the Financial Year using:

DATESYTD('Date'[Date],"31/3")

I am getting closer to the required output but can't seem to get the filter for the current month correct to the requirement shown.

The current results row shows I can get the previous 3 mths average and forecast it to their EOFY with this code:

Current Result = 
CALCULATE(CALCULATE(AVERAGEX( DATESBETWEEN('Date'[Date],
                       DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
        DATEADD(LASTDATE ('Date'[Date]),-1,MONTH ) 
          ) ,
        CALCULATE(SUMX(Sales,DIVIDE(Sales[Amount],Sales[Weighting],0))/1000,FILTER(ALL(Sales[Currency]), Sales[Currency]="AUD"))
                                ),FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY()))),DATESYTD('Date'[Date],"31/3"))

But I can't filter it so it is just for the current month pro-rata.

Certainly I can filter the monthly sales row to show just the current month so it is not because the data is in a Matrix.

Monthly Sales = CALCULATE(CALCULATE(SUMX(Sales,DIVIDE(Sales[Amount],Sales[Weighting],0)/1000),FILTER(ALL(Sales[Currency]), Sales[Currency]="AUD")),FILTER('Date', MONTH('Date'[Date])=MONTH(TODAY())-1))

I am thinking I will need an IF statment as well so if it is the current month then formula pro-rata plus another to forecast to EOFY without the pro-rata.

Any thoughts appreciated.