Hello,
I struggle with seasonal indexing DAX formulas. I have 4 years data - and for them i have created measure like this:
Seasonal_Index= (Measurement[SELECTED_VIEWS_AVG]/(Measurement[ALL_VIEWS_AVG])*100)
SELECTED_VIEWS_AVG = CALCULATE((SUM(Views[Value])/DISTINCTCOUNT(Date[Year AND month number])))
AND
ALL_VIEWS_AVG = CALCULATE(DIVIDE(SUM(Views[Value]),DISTINCTCOUNT(Date[Year AND month number])),ALLEXCEPT(Date,Date[YEAR]))
and it is working correctly - shows seasonality of products on the basis of views value. But it calculates its average on all dates I have so for 4 years and I would like to have every month compared to its year like Jun 2018 compared to average of year 2018 and not all of the years.
I wrote something like this (the same, I added only DATESINPERIOD filter for last 12 months (so Year 4) because I want to compare every year, but I started with Year 4. And there is added "if" condition because I need to have index higher than 120 taken into consideration which will be useful for further calculations)
Seasonal_Index_Y4 = IF(CALCULATE((Measurement[SELECTED_VIEWS_AVG_Y4]/(Measurement[ALL_VIEWS_AVG_Y4])*100) > 120), CALCULATE( (Measurement[SELECTED_VIEWS_AVG_Y4]/(Measurement[ALL_VIEWS_AVG_Y4])*100)))
and
ALL_VIEWS_AVG_Y4 = CALCULATE([AVG_Month_Y4],DATESINPERIOD(Date[Date],MAX(Date[Date]),-12,MONTH) )
And
AVG_Month_Y4 = CALCULATE( DIVIDE(SUM(Views[Value]),DISTINCTCOUNT(Date[Year AND month number])),ALLEXCEPT(Date,Date[Year AND month number]))
AND
SELECTED_VIEWS_AVG_Y4 = CALCULATE((SUM(Views[Value])/DISTINCTCOUNT(Date[Year AND month number])), DATESINPERIOD(Date[Date],MAX(Date[Date]),-12,MONTH) )
it works (I mean does not show errors) however does not show proper seasonalities, shows seasonality on graph on e.g. March when it is not the season for such product. It is not correct.
Thank you for any tips and suggestions if I can use other filter, maybe you notice any mistake in my formulas or maybe I should take other approach to write the fomula to have compared it to seperate year correctly.
Thank you!
