Issue combining VAR and CALCULATE( ..., FILTER

[AbbasAsaria90]

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

[danextian · Accepted 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/

[AbbasAsaria90]

Thanks! I'll take a look at the tutorial and let you know how I get on 🙂

[AbbasAsaria90]

Thanks, this works for the specific example given 🙂

Best,

Abbas