Closing Forecasting for Year and Month

[majid154a]

Sure, here is a clear and professional English translation of the question:

---

At work, I was asked to create a metric called Closing Forecasting, which can be calculated at either the yearly or monthly level.

Year Level

The calculation is based on the average sales up to a selected month, projected over the remaining months of the year.

For example:

• If March 2025 is selected, the yearly Closing Forecasting is calculated by:
  Taking the average sales of January, February, and March, then multiplying it by the number of remaining months in the year (9 months).

• If May is selected, the calculation becomes:
  Average sales of the first five months × remaining months (7 months).

Month Level

The calculation is based on the average sales up to a selected day, projected over the remaining days of the month.

For example:

• If April 10 is selected, the monthly Closing Forecasting is calculated as:
  Average sales of the first 10 days × the remaining days of the month (20 days).

Question

How can this calculation be implemented in a dynamic way so that it automatically adjusts based on the selected month or day?

A Power BI (DAX) example would be highly appreciated.

Thank you very much for your support.

[cengizhanarslan · Accepted solution]

1) Year-level Closing Forecast (select a month)

Closing Forecast (Year) =
VAR MaxSelectedDate =
    MAX ( 'Date'[Date] )
VAR Y =
    YEAR ( MaxSelectedDate )
VAR LastSelectedMonth =
    MONTH ( MaxSelectedDate )
VAR SalesToMonth =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date'[Date] ),
            YEAR ( 'Date'[Date] ) = Y
                && MONTH ( 'Date'[Date] ) <= LastSelectedMonth
        )
    )
VAR AvgMonthlySales =
    DIVIDE ( SalesToMonth, LastSelectedMonth )
VAR RemainingMonths =
    12 - LastSelectedMonth
RETURN
    AvgMonthlySales * RemainingMonths

2) Month-level Closing Forecast (select a day)

Closing Forecast (Month) =
VAR MaxSelectedDate =
    MAX ( 'Date'[Date] )
VAR MonthStart =
    DATE ( YEAR ( MaxSelectedDate ), MONTH ( MaxSelectedDate ), 1 )
VAR DaysInMonth =
    DAY ( EOMONTH ( MaxSelectedDate, 0 ) )
VAR SelectedDay =
    DAY ( MaxSelectedDate )
VAR SalesToDay =
    CALCULATE (
        [Sales],
        DATESBETWEEN ( 'Date'[Date], MonthStart, MaxSelectedDate )
    )
VAR AvgDailySales =
    DIVIDE ( SalesToDay, SelectedDay )
VAR RemainingDays =
    DaysInMonth - SelectedDay
RETURN
    AvgDailySales * RemainingDays

3) One measure that switches automatically (Year vs Month)

If you want a single measure that changes based on what’s on the visual:

Closing Forecast =
IF (
    ISINSCOPE ( 'Date'[Date] ),    
    [Closing Forecast (Month)],
    [Closing Forecast (Year)]
)

[majid154a]

Dear @cengizhanarslan , hope you are well 

Thank you for your help, my brother, and apologies for the earlier mistake in explaining the calculation.

I would like to clarify the correct calculation as follows:

Year Level

If March 2025 is selected, the yearly Closing Forecasting value is calculated by:
Taking the average sales of January, February, and March, then multiplying this average by the total number of months in the year (12 months).

Month Level

If April 10, 2025 is selected, the monthly Closing Forecasting value is calculated as:
The average sales of the first 10 days multiplied by the total number of days in April (30 days).

Question

How can this calculation be made dynamic, so that it automatically adjusts based on the selected month or day?

Thank you very much for your support.

[ryan_mayu]

@majid154a 

I am not sure how you data model looks like. 

i have create a very simple example.

2025/1/1	1	202501
2025/2/1	2	202502
2025/3/1	3	202503
2025/4/1	4	202504
2025/4/10	5	202504
2025/5/1	6	202505

yearlevel = year('Table'[date])*100+month('Table'[date])

then create two measures

Measure =

var _date  =max('Table'[date])

var _month = month(_date)

var _monthleft=12-_month

return sumx(FILTER(ALL('Table'),'Table'[date]<=_date),'Table'[value])/_month*_monthleft

Measure 2 =

var _date=max('Table'[date])

var _yearmonth=max('Table'[yearlevel])

var _dayleft=DATEDIFF(_date,EOMONTH(_date,0),DAY)

var _days=day(_date)

return sumx(FILTER(all('Table'),'Table'[yearlevel]=_yearmonth),'Table'[value])/_days*_dayleft

pls see the attachment below