Calculate values prior to the current month

DAX DAX Calculations
1

Hello,

Our forecast is calculated Actual Sales YTD, less actual sales of current month, plus forecast for current month, and all future periods.

I would like to calculate the forecast using dax, however, but please need some help with the DAX to calculate the forecast values for the months previous to the current month, as it is not correctly calculating in my PBIX attached.

This is my DAX and current result,

Actual Forecast = // Actual Sales YTD, less actual sales of current month, plus forecast for current month, and all future periods.

Var ActualSales = ‘Key Measures’[Actual Sales] - ‘Key Measures’[Current MTD Sales]
Var CurrentMonth = month ( today () )
Var priomonthForecast = CALCULATE ( [Forecast] , filter ( DimDate , DimDate[Date] > CurrentMonth) )
Var ForwardForecast = [Forecast] - priomonthForecast
Return
ActualSales + ForwardForecast

image
image966×310 10.2 KB

image
image814×313 7.83 KB

I would like to be able to calculate the prior months forecast for the forecast accuracy report as well as for this, can i please have some help with this? Thanks for your time!
Forecast Test.pbix (199.5 KB)

2

Hello @Juju,

Thank You for posting your query onto the Forum.

Since you want to calculate the Actuals for the completed month and the Forecasts for the remaining period. Below are the two measures provided for the reference -

1). Actuals + Forecast at a Monthly Level -

Actual Forecast - Monthly - Harsh = 
VAR _Actuals_YTD = 
CALCULATE( SUM( Forecast[Actual ] ) ,
    DimDate[MonthCompleted] = TRUE() )

VAR _Forecast_YTD = 
CALCULATE( SUM( Forecast[Forecast] ) ,
    DimDate[MonthCompleted] = FALSE() )

VAR _Results = 
_Actuals_YTD + _Forecast_YTD

RETURN
_Results

2). Actuals + Forecast at a YTD Level -

Actual Forecast - YTD - Harsh = 
VAR _Actuals_YTD = 
CALCULATE( SUM( Forecast[Actual ] ) ,
    DATESYTD( DimDate[Date] , "31/12" ) , 
    DimDate[MonthCompleted] = TRUE() )

VAR _Forecast_YTD = 
CALCULATE( SUM( Forecast[Forecast] ) ,
    DATESYTD( DimDate[Date] , "31/12" ) , 
    DimDate[MonthCompleted] = FALSE() )

VAR _Results = 
_Actuals_YTD + _Forecast_YTD

RETURN
IF( ISBLANK( [Actual Forecast - Monthly - Harsh] ) , 
    BLANK() , 
    _Results )

Since the current month is not completed yet i.e., today is 31st March, 2022, a forecast figure will be showcased but tomorrow as soon as we enter 1st April, 2022, we’ll see the actuals for the month of March. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results907×255 7.27 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Forecast Test - Harsh.pbix (189.4 KB)

2 Likes
3

Legend, thank you!

4

Hello @Juju,

You’re Welcome!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh