cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbbasAsaria90 Regular Visitor
Regular Visitor

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
danextian New Contributor
New Contributor

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/

 

 

 

 

 

 

"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
danextian New Contributor
New Contributor

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/

 

 

 

 

 

 

"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

AbbasAsaria90 Regular Visitor
Regular Visitor

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

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

AbbasAsaria90 Regular Visitor
Regular Visitor

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

Thanks, this works for the specific example given 🙂

 

Best,

Abbas

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 3,168 guests
Please welcome our newest community members: