cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csudude
Frequent Visitor

Previous 3 Month Avg NOT Including current month

I've looked all over but struggling with this DAX.  What I need is the average Registrations of the previous 3 months NOT including the current month. My data source is at the day level and I've rolled it up to the month level below.

 

I have a measure called [Total Registrations] = SUM(Digital_sp_Rpt_Registrations[Registrations])

 

The average measure I created (obviously doesn't work) is the following =

 

3 Month Avg =
VAR period_end = FIRSTDATE(Dates[Date])
VAR period_start = DATEADD(period_end,-3,MONTH)
return
CALCULATE(AVERAGEX ( VALUES ( Dates[Month Year] ), [Total Registrations] ),
DATESBETWEEN(Dates[Date],period_start,period_end))

 

However, the above measure gives me the following: 

 

image.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

In the example above "3 Month Avg" for Sep 2019 should be the average of Jun, Jul and Aug. It should be 33077, not 25015.

Any suggestions? 

 

1 ACCEPTED SOLUTION
csudude
Frequent Visitor

Thanks @kentyler for your reply.  It didn't exactly answer my question but it got me thinking in regards to the dates I was using.  

 

For September 2019 the period_start date was 6/1/2019 and the period_end date was 9/1/2019.  Those were the values I was expecting to get back.  But, I started playing around with the end date and changed it to 8/31.  That eneded up being the issue.  

 

Here is what finally worked for me:

3 Month Avg =
VAR period_end = EOMONTH(FIRSTDATE(Dates[Date]),-1)
return
CALCULATE(AVERAGEX ( ALL(Dates[Month Year]), [Total Registrations] ),
DATESINPERIOD(Dates[Date],period_end,-3,MONTH))

View solution in original post

3 REPLIES 3
csudude
Frequent Visitor

Thanks @kentyler for your reply.  It didn't exactly answer my question but it got me thinking in regards to the dates I was using.  

 

For September 2019 the period_start date was 6/1/2019 and the period_end date was 9/1/2019.  Those were the values I was expecting to get back.  But, I started playing around with the end date and changed it to 8/31.  That eneded up being the issue.  

 

Here is what finally worked for me:

3 Month Avg =
VAR period_end = EOMONTH(FIRSTDATE(Dates[Date]),-1)
return
CALCULATE(AVERAGEX ( ALL(Dates[Month Year]), [Total Registrations] ),
DATESINPERIOD(Dates[Date],period_end,-3,MONTH))

View solution in original post

kentyler
Solution Sage
Solution Sage

CALCULATE(AVERAGEX ( VALUES ( Dates[Month Year] ), [Total Registrations] ),
DATESBETWEEN(Dates[Date],period_start,period_end))

 

the evaluation context that is rolling up to the day level is not changed here... so the dates retrieved by DATESBETWEEN, is limited to the dates in the evaluation context for each row

you might have to all an ALL(Dates) as part the the filters for CALCULATE, which will removed any filters on the dates that are coming from the exisiting evaluation context for any given row





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


kentyler
Solution Sage
Solution Sage

did you try having the measure return period end, or period start, to see if they are the values you think they should be





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors