monthly forecast based on known monthly percentages

[fklatecki]

Greetings,

I am looking for advise/answers the following scenario:

We have known sales by month, and I have calculated a percentage table:

As this is now May we have for most customers actual sales to the end April, or sub-total of percentages, 6.23 + 6.01 + 9.18 + 6.57 = 27.99 percentage of sales YTD. We would like to forecast monthly sales for the remaining 8 months using the remaining percentages to obtain a forecast the rest of the year.

i.e. Actual sales YTD $500,000 (27.99), Forecast for entire year $1,786,352.27 

How could this be dynamically calculated based on current month i.e. next month where percentage is 34.72, and YTD to sales would based on customers YTD sales?

Best regards and your help is genuinely appreciated.

[ImkeF]

Hi @fklatecki ,
hard to tell without seeing the data.
But it would happen if there no sales in may for Company 2 in May.
Then your model with fixed % for the forecast wouldn't work, as the May-% would be applied to null and things wouldn't add up to 100%.

[ImkeF]

Hi @fklatecki ,
I don't understand your last response, but this is what I meant with the YTD:

So for my experience, there is a good chance that something with your date logic in your model or your YTD Measure.
If that isn't the problem, I'd suggest to play around with the file I've posted to try to replicate the error you have in your model.

[fklatecki]

Thank you for your time and guidance. I will dig in and try to resolve the details.

Best.

[fklatecki]

Greetings, thank you for pointing me a direction.

Is it the Actual "SumForecast"  i.e.:

VAR _previousMonth =

CALCULATE (

MAX ( 'Calendar'[MonthNum] ), -- Must be the column that is connected to the Forecast-table

REMOVEFILTERS ( 'Calendar' ),

'Calendar'[Date] = TODAY ()

) - 1

Or a meaure or one of the "YTD" measures used in "SumForecast"

YTD SumSales = TOTALYTD(

SUM('Sales History'[Gross Sales]),

'Sales History'[Date])

YTD SumPercentage =

CALCULATE([SumPercentage],DATESYTD('Calendar'[Date]))

Best.

[fklatecki]

Excellant. A few minor modifications and things look good!
Very much appreciated!!!!

[ImkeF · Accepted solution]

Hello @fklatecki ,
you can use a measure like this to achieve that goal:

SumForecast = 
VAR _previousMonth =
    CALCULATE (
        MAX ( 'Date'[Month Number] ), -- Must be the column that is connected to the Forecast-table
        REMOVEFILTERS ( 'Date' ),
        'Date'[Date] = TODAY ()
    ) - 1
VAR _latestYTD =
    CALCULATE (
        [YTD SumSales],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Month Number] = _previousMonth
    )
VAR _cumulPercentage =
    CALCULATE (
        [YTD SumPercentage],
        REMOVEFILTERS ( 'Date' ),
        'Date'[Month Number] = _previousMonth
    )
VAR _amountForTotalYear =
    DIVIDE ( _latestYTD, _cumulPercentage )
VAR _result =
    SUMX (
        'Forecast',
        VAR _isPlanningMonth =
            ( CALCULATE ( MAX ( 'Date'[Month Number] ) ) > _previousMonth )
        VAR _relevantYTD =
            IF ( _isPlanningMonth, _amountForTotalYear * Forecast[ % Forecast], [SumSales] )
        RETURN
            _relevantYTD
    )
RETURN
    _result

Please check the file enclosed to understand the modelling requirements.

[fklatecki]

Greetings,

I think I have sorted out my key issues with this PBI report. Perhaps you can explain this discrepancy:

(using live Sales data) As you can see Company 1 shows a complete 12 month list of SumForecast data, while Company 2 shows a gap for May 2022. Any thoughts you have would be appreciated.

Again thank you for your help and guidance.

Company 1:

Company 2 

[fklatecki]

Greetings and thank you for your assistance.

I am working with your solution, and I have an anomoly with the data:

Perhaps, I did not explain my requirements, but I do have some questions. Look at this clients sales history (Jan to April) we see the posted "real" values. We are missing May and June, and then the forecast amounts for Jul to Dec, seem abnormally low. 2021 actual sales were $11.6 million. This is the current formula for SumForecast:

SumForecast =

VAR _previousMonth =

CALCULATE (

MAX ( 'Calendar'[MonthNum] ), -- Must be the column that is connected to the Forecast-table

REMOVEFILTERS ( 'Calendar' ),

'Calendar'[Date] = TODAY ()

) - 0

VAR _latestYTD =

CALCULATE (

[YTD SumSales],

REMOVEFILTERS ( 'Calendar' ),

'Calendar'[MonthNum] = _previousMonth

)

VAR _cumulPercentage =

CALCULATE (

[YTD SumPercentage],

REMOVEFILTERS ( 'Calendar' ),

'Calendar'[MonthNum] = _previousMonth

)

VAR _amountForTotalYear =

DIVIDE ( _latestYTD, _cumulPercentage )

VAR _result =

SUMX (

'Forecast',

VAR _isPlanningMonth =

( CALCULATE ( MAX ( 'Calendar'[MonthNum] ) ) > _previousMonth )

VAR _relevantYTD =

IF ( _isPlanningMonth, _amountForTotalYear * Forecast[% Forecast], [SumSales] )

RETURN

_relevantYTD

)

RETURN

_result

Your thoughts would be again greatly appreciated.

Best.

[ImkeF]

Hi @fklatecki ,
hard to tell, but what I can see is that your YTD isn't working (as I would normally expect).
Maybe you're using the wrong dates?