Forum Discussion
Current Month Calc, forecast and Pro rate
Hi Binway
It seems you may need a calendar date table. If it is not your case, please share some data sample file. You can upload it to OneDrive or Dropbox and post the link here.
Regards,
Cherie
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.