Forecasting Lead Volume Using Moving Average

[vehloaaron]

PBI Community - Looking for some help on a using a moving average to forecast out where my lead or sales actuals will end up for at quarter end.  Researching in the community I can see mix results of others trying this.  At the end of the day I'd like to use some run rate to forecast.  I was able to accomplish this in Excell using Average w/ Offset on a date and sum values.  In PBI I am hitting a snag when adding the measures to my to my table here is what I have, any help on this would be greatly appreciated

MEL Count = CALCULATE(COUNT(Leads[MEL Date Only]),Leads[MEL Date Only]<>BLANK(),USERELATIONSHIP(Leads[MEL Date Only],'Date'[Date]))

MEL 60 Days Count = (CALCULATE([MEL Count],DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-61,DAY)))

 

MEL 60 Day Avg. = DIVIDE([MEL 60 Days Count],60,BLANK())

Days Remaining In Quarter = DATEDIFF(TODAY(),ENDOFQUARTER('Date'[Date]),DAY)

EOQ MEL Forecast = [MEL 60 Day Avg.]*[Days Remaining In Quarter]

Have the following tables: 

Date--> Montly Target --> Status 

MEL Date

Date

So a target table related to date table and a table with data with leads that I coun the mel date.  

[v-zhangti]

Hi, @vehloaaron 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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