Forum Discussion
Rolling average per category with date table
Hi,
I have a simple PM dataset containing product category, date and score (1-10).
| Product | Date (YYY-MM-DD) | ITV Score |
| BS | 2023-01-27 | 9.0 |
| PS | 2023-01-20 | 8.2 |
| LP | 2023-01-19 | 7.0 |
| VO | 2023-01-17 | 7.5 |
| PN | 2023-01-25 | 7.8 |
| etc |
Products belong to product group B2B or Retail.
I have a date table based on the PM dates: zzCalendar = CALENDAR(MIN(PM[DATE]);MAX(PM[DATE])).
I have a measurement
ITV rolling avg =
var _ITV = AVERAGEX(values(zzProduct[PRODUCT]); CALCULATE([ITV];DATESINPERIOD(zzCalendar[Date];max(zzCalendar[Date]);-365;DAY)))
return
if (HASONEVALUE(zzProduct[PRODUCT]);_ITV; AVERAGEX(values(zzProduct[PRODUCT]); ROUND(_ITV;2)))
The measurement calculates the average score for each product and for each aggregation (Product group and Total) it calculates the average of the products (rounded on 2 decimals).
Problem with this measurement is that max(zzCalendar[Date]) or lastdate(zzCalendar[Date]) returns the lastdate for all product categories. But the product categories have different lastdates. For each product category the daterange for the calculation is different.
So I tried a different approach:
ITV rolling avg =
var _ITV = AVERAGEX(values(zzProduct[PRODUCT]); CALCULATE([ITV];DATESINPERIOD(zzCalendar[Date];max(PM[Date]);-365;DAY)))
return
if (HASONEVALUE(zzProduct[PRODUCT]);_ITV; AVERAGEx(values(zzProduct[PRODUCT]); ROUND(_ITV;2)))
This formula returns the rights rolling averages, but when I put the rolling average in a line chart to see the trend it gives unwanted results. The rolling average drops sharply and the number of measurements drops from around 400 to 9. It has got to do with the fact that in december last year there were no measurements for B2B.
If I know how I would have inserted a dataset and a pbix, but I hope somebody can help?
No RepliesBe the first to reply