Forecasting Current Month Revenue Based on Average last 30 days Revenue

[Anonymous]

Hello Community

I’m trying to forecast my current month revenue by (Average Rate * no of patient) derived from different business location Revenue dividing by number of patient to average rate . Is there any way my to forecast revenue based on last 15 to 30 days or last month for any new month? Currently, I have built the following calculation based on average daily revenue. But it’s not efficient and accurate.

Currently I’m averaging all the revenue starting from Feb. But forecasted number with the average looks off. Also used Forecasting PowerBI feature. But I’m not sure how to calculate the actual and forecasted value together in Power Bi to calculate total for the month.

Daily Revenue = CALCULATE(SUM(Census[Total_Estimate_Revenue]),DATEADD('Calendar'[Date],0,MONTH))

Forecasted = IF (

    ISBLANK ( SUM ( Census[Total_Estimate_Revenue]) ),

AVERAGEX

        (CALCULATETABLE (VALUES ( 'Calendar'[Date] ),

            ALLEXCEPT ( 'Calendar', 'Calendar'[Date], 'Calendar'[Month] )),

        (CALCULATE ( SUM ( Census[Total_Estimate_Revenue] ) ) )))

Actual +Forecast = ([Daily Revenue] + [Forecasted])

Current Forecast

Highlighted is not able to sum the calculation

Difficulties

1. Taking average of all months is not sensible idea for us because patient might be changing facility or anything but averaging last 15 to 20 days revenue can be ideal.
2. Should we focus on forecasting number of patient or just focus on revenue?
3. Can we update the forecast and adjust forecast automatically with the data refresh?
4. Any other efficient way to forecast revenue or estimate

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

1. Try to create formula like this.

Actual +Forecast =
IF (
    ISBLANK ( SUM ( Census[Total_Estimate_Revenue] ) ),
    AVERAGEX (
        FILTER (
            ALL ( census ),
            [dateid] <= MAX ( [dateid] )
                && [dateid]
                    > MAX ( [dateid] ) - 15
        ),
        [Daily Revenue]
    ),
    [Daily Revenue]
)

3. After the data is refreshed, the measure will also be refreshed

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.