Combining moving average 'DATESINPERIOD' with FILTER in CALCULATE formula

[AbbasAsaria90]

Hi everyone,

I have a table called pd_nb_leads, which is a table of our sales leads. One of the columns is `Date deal created`.

I'm looking to create a graph showing the moving average of deals created in the last 28 days for different people in our organisation. I would like to show on the graph this figure for the company average, and also the individual people. This is why I am doing one measure for the average and each of these people, as the legend functionality doesn't allow this.

I have a table 'X Axis: Date Created' with my dates (which has a relation to `Date deal created`, and another table Calendar_rolling_axis_created with the dates from the X Axis table, and a relationship between them. I have been able to get the company wide figure with the following formula

Avg leads created, last 28 days = 
CALCULATE (
COUNT ( pd_nb_leads[Deals] ),
DATESINPERIOD ( Calendar_rolling_axis_created[Date], LASTDATE ( Calendar_rolling_axis_created[Date] ), - [# days for rolling avg mean], DAY )
) / [# days for rolling avg mean]

where [# days for rolling avg mean] is a measure that is equal to 28, which is the period we want to do the rolling average for

If a deal belongs to someone, their name will appear in the owner_stage_1 column in the pd_nb_leads table. This is why I wanted to do a formula like this to return the number of deals someone has created in the last 28 days

Leads created, last 28 days, name = 
CALCULATE (
COUNT ( pd_nb_leads[Deals] ),
DATESINPERIOD ( Calendar_rolling_axis_created[Date], LASTDATE ( Calendar_rolling_axis_created[Date] ), - [# days for rolling avg mean], DAY ),
FILTER(pd_nb_leads, pd_nb_leads[owner_stage_1] = "Fname Lname")
)

However, this returns for each date purely the number of deals created on a particular date, and not the sum of deals created in the last 28 days.

Is there something I'm missing in terms of combining DATESINPERIOD and FILTER like this?

Thanks,

Abbas

[v-yulgu-msft]

Hi @AbbasAsaria90,

Would you please provide some sample data and show us your desired output? And what is th formula of [# days for rolling avg mean]?

Regards,

Yuliana Gu

[AbbasAsaria90]

Hi Yuliana @v-yulgu-msft

Did the attached pbix file and excel calculations make sense?

Thank you,

Abbas

[AbbasAsaria90]

Thank you,

To simplify a little, I've put some sanitised lead data in a pbix file linked here

The data I'm expecting is

And what I end up with is this

So the original problem again, when I add an additional filter into the calculate sum, it doesn't count for the dates in the period, but only the date itself

Thanks