Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Hi Yuliana @v-yulgu-msft
Did the attached pbix file and excel calculations make sense?
Thank you,
Abbas
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |