cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Issue combining VAR and CALCULATE( ..., FILTER

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)))
```

VAR picture.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Issue combining VAR and CALCULATE( ..., FILTER

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/

 

 

 

 

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: Issue combining VAR and CALCULATE( ..., FILTER

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/

 

 

 

 

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

View solution in original post

Highlighted
Helper I
Helper I

Re: Issue combining VAR and CALCULATE( ..., FILTER

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

Highlighted
Helper I
Helper I

Re: Issue combining VAR and CALCULATE( ..., FILTER

Thanks, this works for the specific example given 🙂

 

Best,

Abbas

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors