Forum Discussion

Binway's avatar
Binway
Icon for Helper II rankHelper II
8 years ago

Current Month Calc, forecast and Pro rate

Hi Folks, I a financial year starting in April where I need to calculate the current months average for the previous 3 months and forecast to the end of the year, but with the current month showing ...
Need Help
v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

Binway's avatar
Binway
Icon for Helper II rankHelper II
8 years ago

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")