cancel
Showing results for
Did you mean:
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])
return
CALCULATE(AVERAGEX ( VALUES ( Dates[Month Year] ), [Total Registrations] ),
DATESBETWEEN(Dates[Date],period_start,period_end))

However, the above measure gives me the following:

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
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))
3 REPLIES 3
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))
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

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

Announcements