I've been asked to present the percentage difference in the rolling averages of Products Shipped vs Products Forecasted.
I need to be able to show by Product, and within the provided 2 year time frame, the percent differences at 3 month rolling, 6 month rolling, 9 month.....and ending at 24 month, and all instances of are to be shown.
So far I've only been able to come up with six seperate DAX measures, one for each rolling month instance.
One would think that there should be a better way.
Below is an example of 3 month rolling average % difference measure I am using. For each instance I have been changing the name and the NumOfMonths amount.
3 Mo. Produc Rolling Avg % Diff =
VAR NumOfMonths = -3
VAR LastCurrentDate = MAX('Dates'[Date])
VAR Period = DATESINPERIOD('Dates'[Date], LastCurrentDate, NumOfMonths, MONTH)
VAR FC_RollingAvg =
CALCULATE(
AVERAGEX(
VALUES('Dates'[Month & Year]),
COALESCE([Product FC Total], 0)
),
Period
)
VAR Sales_RollingAvg =
CALCULATE(
AVERAGEX(
VALUES('Dates'[Month & Year]),
COALESCE('Orders Taken Measures'[Orders Taken], 0)
),
Period
)
VAR FirstDateInPeriod = MINX(Period, 'Dates'[Date])
VAR LastDateWithSales = MAX('BOI Orders Taken'[DATE_CREATED])
RETURN
IF(
FirstDateInPeriod <= LastDateWithSales,
DIVIDE(Sales_RollingAvg - FC_RollingAvg, FC_RollingAvg, 0)
)
