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.
I have a table called `pd_nb_leads`, which is a table of our sales leads. One of the columns is `Date deal created`, and another is `Deal Origin`.
I'm looking to create a graph showing the moving average of deals created in the last 28 days, which I can then manipulate with a slicer based on the entries in the column `Deal Origin`. The first step is returning for a given date, the number of deals created in the last 28 days
I originally made a calculated column in a date table but that was not affected by the slicer, which led me to believe a measure on the date table would be the best approach. For the date table I created the following two measures, which show the following values in a table with the Date field from the Date Table in the X axis
```
Leads created, day =
VAR currentDate =
average('X Axis: Day Created'[Date])
RETURN
CALCULATE(
COUNT(pd_nb_leads[Deals]),
FILTER(pd_nb_leads, (pd_nb_leads[Date deal created] = currentDate)))
```
```
Leads created, last 28 days =
VAR currentDate =
average('X Axis: Day Created'[Date])
RETURN
CALCULATE(
COUNT(pd_nb_leads[Deals]),
FILTER(pd_nb_leads, (pd_nb_leads[Date deal created] <= currentDate
&& pd_nb_leads[Date deal created] > currentDate - 28)))
```
It's counterintuitive to me that they both return the same value, and also strange that if you have the filter show something like `pd_nb_leads[Date deal created] > currentDate` - the measure returns blank in the table
How would I correct the measure `Leads created, last 28 days` ?
Thanks very much
Abbas
Solved! Go to Solution.
Hi @AbbasAsaria90,
For your measure, if your CurrentDate variable is simply equal to the dates in the date column then you can simply write your formula as
Leads created, last 28 days = COUNT ( pd_nb_leads[Deals] )
because hen you place your Date colum and the Leads Created, day measure inside the table, the value that is being returned by the measure is being filtered by the value in the Date column.
Now for your second measure, the same thing happens. It is being filtered by the value from the Date column. Thus your seeing the same result. To achieve your desired result, you need to create a separate Calendar table and then create a relationship between the generated date column from Calendar and Date column from your fact table. You can create in a calculated calendar table in dax by using CALENDAR FUNCTION. Example:
CALENDAR = CALENDAR ( MIN ( 'X Axis: Day Created'[Date].DATE ), MAX ( 'X Axis: Day Created'[Date].DATE ) )
which is dynamically created based on the earliest and latest dates from Fact. Now, create another measure
Leads created, last 28 days = CALCULATE ( COUNT ( pd_nb_leads[Deals] ), DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -28, DAY ) )
Put this measure and the date table from calendar to a table.
You can find a good tutorial here: https://powerpivotpro.com/2013/07/moving-averages-sums-etc/
Proud to be a Super User!
Hi @AbbasAsaria90,
For your measure, if your CurrentDate variable is simply equal to the dates in the date column then you can simply write your formula as
Leads created, last 28 days = COUNT ( pd_nb_leads[Deals] )
because hen you place your Date colum and the Leads Created, day measure inside the table, the value that is being returned by the measure is being filtered by the value in the Date column.
Now for your second measure, the same thing happens. It is being filtered by the value from the Date column. Thus your seeing the same result. To achieve your desired result, you need to create a separate Calendar table and then create a relationship between the generated date column from Calendar and Date column from your fact table. You can create in a calculated calendar table in dax by using CALENDAR FUNCTION. Example:
CALENDAR = CALENDAR ( MIN ( 'X Axis: Day Created'[Date].DATE ), MAX ( 'X Axis: Day Created'[Date].DATE ) )
which is dynamically created based on the earliest and latest dates from Fact. Now, create another measure
Leads created, last 28 days = CALCULATE ( COUNT ( pd_nb_leads[Deals] ), DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -28, DAY ) )
Put this measure and the date table from calendar to a table.
You can find a good tutorial here: https://powerpivotpro.com/2013/07/moving-averages-sums-etc/
Proud to be a Super User!
Thanks! I'll take a look at the tutorial and let you know how I get on 🙂
Thanks, this works for the specific example given 🙂
Best,
Abbas
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |