Current Month Calc, forecast and Pro rate

[Binway]

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 pro-rata data based on the days left in the month.

The Matrix would look like this:

Values	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Totals
Monthly Sales	645.053	837.207	955.129	811.420	354.521								3603.330
Avg Prevs 3 mths						612.750	707.023	707.023	707.023	707.023	707.023	707.023	4854.888

I found how to forecast using the DATESYTD with the End of Financial Year, and got the average to work in one of my attempts by dividing by 3 which is fine but can't quite get it all together.  This is the best example I have so far.

CALCULATE(
                  CALCULATE(
CALCULATE(CALCULATE(CALCULATE(SUMX(Sales,DIVIDE(Sales[Amount],Sales[Weighting],0)/1000),FILTER(ALL(Sales[Currency]), Sales[Currency]="AUD")),
DATESBETWEEN('Date'[Date],
                       DATEADD ( FIRSTDATE ('Date'[Date] ), -3, MONTH ),
        DATEADD(LASTDATE ('Date'[Date]),-1,MONTH )                                        ))
   ),FILTER('Date', 'Date'[Date]=EOMONTH(TODAY(),0))),DATESYTD('Date'[Date],"31/3")
      ,FILTER('Date', 'Date'[Month #]=MONTH(TODAY())))/'Date'[DaysinCurrentMonth] *'Date'[DaysLeftCurrentMonth]

Appreciate the help.

Binway

[v-cherch-msft]

Hi @Binway

Here are some references for you:

https://community.powerbi.com/t5/Desktop/Forecasting-future-months-using-previous-months-data-on-a-f...

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Regards,

Cherie

[Binway]

Hi Cherie,

Got the Forecast code working.  Seems my Month/Year needs to be a date.

Hopefully can tweak this to suite my needs.

[v-cherch-msft]

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]

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.

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

[Binway]

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

[Binway]

Hi Cherie,

Got the Forecast code working.  Seems my Month/Year needs to be a date.

Hopefully can tweak this to suite my needs.

[Binway]

Thanks Cherie it looked like the code and resutls in the "Forecasting Future Months" link would do the job. Your code looks like this:

Measure =
IF (
    ISBLANK ( SUM ( 'Actual Spend'[Corporate Card Spend] ) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES ( 'Calendar Dates'[Month/Year] ),
            ALLEXCEPT ( 'Calendar Dates', 'Calendar Dates'[Fiscal Year] )
        ),
        CALCULATE ( SUM ( 'Actual Spend'[Corporate Card Spend] ) )
    )
)

I thouhgt I would start with a simple replication of your code which is not that different:

AA1_FrmDskFrm = 
IF (
    ISBLANK (SUM([Amount]) ),
    AVERAGEX (
        CALCULATETABLE (
            VALUES('Date'[Month-year] ),
            ALLEXCEPT ( 'Date','Date'[Financial year])
        ),
        CALCULATE(SUM([Amount]))
    )
)

But the result is not similiar - have I got to do something different in the Date table perhaps.Below is the result. Sorry just started reading Alberto's Definitive Guide to DAX so have bit to learn. results I get

 

Regards

Binway

[Greg_Deckler]

See if my Time Intelligence the Hard Way Quick Measure helps: 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...