Forum Discussion

csudude's avatar
csudude
Frequent Visitor
6 years ago
Solved

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 roll...
  • csudude's avatar
    csudude
    6 years ago

    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))
csudude's avatar
csudude
Frequent Visitor
6 years ago

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