Historical Average of Measure

[yaegerck]

Hello, I'm in need of help creating a dynamic measure in Power Bi. I want to create a measure that takes the average cumulative sales with X days left divded by the forecast and do this for each month. I then need a measure that averages these rates by how many days are left in the month. So for example if we are on sepetember 18th I want to know on average of all prior months what percent of sales against the forecast were we with 12 days remaining in the month.

Below I will provide the scope and a sample layout for my data. I can't share actual data due to it being sensitive so I created a fake set. Also There exists more columns in the actual dataset I will use to filter this measure hence why it needs to be a measure and not a calculated column.

My Setup - Table relationships

Sample Data


Measure steps
First we need to compute the sales / forecast then average those on days left in month for all prior months and this is to be displayed side by side with the current month sales / forecast. Note: This first part cannot be a calculated column becuse in the real data I will be using other columns as filtered for different product lines.

Desired Output below... I have tried for days to figure this out but am no where close. If anyone can help it would be tremendously appreciated. Thank you

[V-pazhen-msft]

@yaegerck 

Can you put the sample in a pbix, upload to somewhere like Onedrive for business and then share the link. 

And I am not clear with your the description of your expected output as well as the example, especially the X days left divided by the forecast and do this for each month.

"I want to create a measure that takes the average cumulative sales with X days left "

Can you try elaborate.

Regards
Paul

[yaegerck]

@V-pazhen-msft Thank you for your response and I apologize for being unclear. Below I have attached a link to a BOX download to the PBI File of the sample data. 

https://app.box.com/s/uh0obacu6wvp011ubq76lwlfl91xfeo4

As for my question, what I am hoping to have displayed on a high level is the current months sales by day vs the average of sales divided by the forecast with X days left in the month. So in the graph I attached last time is what I want to see. It shows one line which is the current months sales divided by it's forecast, the other line is the average sales/forecast with 28 days left in the month and 14 days left in the month because in September (Current Month) the two dates have 28 days left and 14 days left. Behind the scenes It should filter all dates prior to this month then calculate the Cumulative sales at that date and divide it by the forecast. Then Groupby the 'Days Left' column to obtain the average of those cumulative sales/forecast number and display this on the same axis as the current month.

Please let me know if there is further clarification needed. Conceptually I understand this and coul od it quite easily with Python but I am really hoping to use that as a last resort because of how slow it becomes. Thanks again for taking the time to look at my problem!

[yaegerck · Accepted solution]

Measure =

VAR cumul =  CALCULATE(TOTALMTD(SUM(SalesData[Sales]), SalesData[Date]), ALLEXCEPT(SalesData, SalesData[Date]))

VAR avg     =  AVERAGEX(VALUES(SalesData[Date]), cumul)/ CALCULATE(SUM(Forecasts[Forecast]), USERELATIONSHIP(Forecasts[YearMonthNo], SalesData[YearMonthNo]))

VAR filt = FILTER(ALLEXCEPT(SalesData, SalesData[Days Left]), SalesData[Date].[MonthNo] < CALCULATE(MAX(SalesData[Date].[MonthNo]), ALLSELECTED(SalesData)) && SalesData[Date].[Year] = CALCULATE(MAX(SalesData[Date].[Year]), ALLSELECTED(SalesData)))

RETURN CALCULATE(AVERAGEX(filt, [avg]), VALUES(SalesData[Days Left]))

[yaegerck]

EDIT: YearMonthNo should be 4 not 3 for Dates in September