Sales forecasting - season index, median of the season index, avg per year - please need help!!!

[pani_victoria]

Hello!

Please help me with the calculation of the seasonality index and its median.

I tried to solve the problem this way

1) I calculate average sales for each year, only for closed months

AVG_for_YEAR =
var _sales = CALCULATE(
'#measures'[Sales Amount], 'Date'[ClosePeriodMonth] = TRUE(),
FILTER(ALLSELECTED('Date'),'Date'[Year]=MAX('Date'[Year] )))

var _months = CALCULATE(
DISTINCTCOUNT('Date'[Month]),'Date'[ClosePeriodMonth] = TRUE(),
FILTER(ALLSELECTED('Date'),'Date'[Year]=MAX('Date'[Year] )))

RETURN DIVIDE(_sales, _months, 0)

The mathematical expectations are correct

2) Seasonality index = sales per month/average per year

Season Index = CALCULATE(DIVIDE('#measures'[Sales Amount], '#measures'[AVG_for_YEAR], BLANK()), 'Date'[ClosePeriodMonth] = TRUE())

The mathematical expectations are correct

3) Median of the seasonality index for N years

Median =
CALCULATE(
MEDIANX( VALUES('Date'[Year Month]), '#measures'[Season Index]),
DATESINPERIOD( 'Date'[Date], MAX('Date'[Date]), -4,YEAR)
)

But here the median shows me incorrect results.

And I don't know where the mistake is.
I am attaching pbix for research

I have been trying to solve the issue for a long time now, and it just won't go away (
I really need help!

download pbix 

[pani_victoria · Accepted solution]

AVG_for_YEAR = 
var _max_date = MAX('Date'[Date]) 
var _datesInPer = DATESBETWEEN('Date'[Date],DATE(YEAR(_max_date),1,1),DATE(YEAR(_max_date),12,31)) 
var _sales = CALCULATE( '#measures'[Sales Amount], 'Date'[ClosePeriodMonth] = TRUE(), _datesInPer) 
var _months = CALCULATE( DISTINCTCOUNT('Date'[Year Month]),'Date'[ClosePeriodMonth] = TRUE(), _datesInPer) 
RETURN DIVIDE(_sales, _months, 0) 

the median I use is

Median 2 = 
var _max_date = MAX('Date'[Date]) 
var _min_date = CALCULATE(MIN('Date'[Date]),ALL('Date')) 
var _month = MONTH(_max_date) 
var _datesInPer = DATESBETWEEN('Date'[Date], _min_date,_max_date) 
var tab = CALCULATETABLE( ADDCOLUMNS(VALUES('Date'[Year Month]), "@Val", '#measures'[Season Index1]),_datesInPer, MONTH('Date'[Date]) = _month) 
var res = MEDIANX(FILTER(tab, [@Val] <> BLANK()),[@Val]) 
return res 

Maybe someone will find it useful!

These calculations are used to forecast sales.

1. I calculate the trend for 2 years
2. I calculate the seasonality index and its median for 5 years
3. I multiply the trend by the median and get a forecast

 

I'm attaching a pbix with the forecast.

If someone can improve this model or fix the errors, it will be useful for me and the community.

I thank everyone who sincerely tried to help.

download pbix 

[v-rzhou-msft]

Hi @pani_victoria ,

I think you can try code as below to update your measures.

Median1 = 
MEDIANX(
    FILTER (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[ClosePeriodMonth] = TRUE() ),
            'Date'[Year Month],
            'Date'[Month],
            "Index", [Season Index]
        ),
        [Month] = MAX ( 'Date'[Month] )
    ),
    [Index]
)

Here I also update the AVG measure.

AVG_for_YEAR1 = 
AVERAGEX (
    FILTER (
        SUMMARIZE (
            FILTER ( ALLSELECTED ( 'Date' ), 'Date'[ClosePeriodMonth] = TRUE() ),
            'Date'[Year Month],
            'Date'[Year],
            "Sales", [Sales Amount]
        ),
        [Year] = MAX ( 'Date'[Year] )
    ),
    [Sales]
)

Result is as below.

Best Regards,
Rico Zhou

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

[johnt75]

AVG_for_YEAR = 
VAR MaxYear = MAX('Date'[Year])
VAR _sales = CALCULATE(
    '#measures'[Sales Amount],
    REMOVEFILTERS('Date'),
    'Date'[ClosePeriodMonth] = TRUE(),
    'Date'[Year] = MAX('Date'[Year])
)

VAR _months = CALCULATE(
    DISTINCTCOUNT('Date'[Month]),
    REMOVEFILTERS('Date'),
    'Date'[ClosePeriodMonth] = TRUE(),
    'Date'[Year] = MAX('Date'[Year])
)

RETURN
    DIVIDE(
        _sales,
        _months,
        0
    )

[Median] = CALCULATE(
MEDIANX(
	VALUES('Date'[Year Month]),
	CALCULATE(
		'#measures'[Season Index],
		ALLEXCEPT(
			'Date',
			'Date'[Year Month]
		)
	)
),
KEEPFILTERS(DATESINPERIOD(
	'Date'[Date],
	MAX('Date'[Date]),
	-5,
	YEAR
))
)

this seems to match the numbes in the image from the report.

The initial code was only going back 4 years, so 2020 was excluded, and the ALLSELECTED in the average measure wasn't working as 'Date'[Year Month] wasn't being used in the visual.