Rolling average help! Needing to group by month in calculation.

[Beckster]

Hi All

I've spent around 3 hours trying to get this one formula to work but just can't get it to work right.

I'm trying to create a 3 month rolling monthly average that doesn't require all the months to be displayed to calculate (ie filter all()). (ie if i am only filtering on 2016, Jan should still show a 3 month average from Oct-Dec 2015, not only Jan results because of the year filter).

The measure is a distinct count of opportunities (row of data) for the month.

I've tried averagex but I was getting a daily average, not an average based on the total count of the month.

The closest I have gotten to is to try and sum up the months previous and divide by 3, but some of the months just don't look right.

A few options I have tried are: (at this am just trying to do the sum so I can just easily do a divide by 3)

3 Month Avg = CALCULATE([# Opps],Summarize( FILTER ( ALL ( vw_dimdate ), vw_dimdate[date] > ( MAX ( vw_dimdate[date] ) - 3 ) && vw_dimdate[date] <= MAX ( vw_dimdate[date] ) ),vw_dimdate[fmonthkey],vw_dimdate[fmonth]) )

3 Month Avg = CALCULATE(counta(vw_fact_opportunity[opportunity_id]),all(vw_dimdate),DATESINPERIOD(vw_dimdate[date],LASTDATE(vw_dimdate[date]),-3, Month))

Any help/guidance would be appreciated.

Cheers!

[Sean · Accepted solution]

@Beckster Let me know if these work for you.

[BrentL]

Hello,

I am new to Power BI and DAX. 

I beleive you are using this as a measure.

I am trying to get this to work but I cannot seem to get it to sum over a 12 month period.  Sumrec is a measure that is sum(received). I am not sure why it is not totaling. when I test the lastdate() Fx I get 4/21/2016 and the first date is 1/2/2012.  I hope this is enough information to help solve what I am missing.

 Any help is much appreciated.

thank you,

[Sean]

@BrentL What table are the Year and Month columns in?

If a Calendar Table you have to point the time functions to CalendarTable[Date]

[BrentL]

I was not using a year and month column it was a date column with a heirarchy applied minus the quarter and day.  Should I make a month and year column and use that instead?  Do I then point to the month column?

thank you,

[Sean]

where is the sumrec measure? in the Receipts table as well?

what date did you use to create the hierarchy?

This formula should work! it's basically your 12 month trailing total

[BrentL]

@Sean Thank you for the help on this.  The picture below shows the table I am using for all the fields with the Sumrec formula.  The "receipt date" is the date out of the system. The "received" field is the quantity we received.  The other two are "New Measures" Using the "receipt date and "received" Fields.   Thank you again for all the help. 

What is the formula you have for the Total Sales?  That maybe where I am off.

[Sean]

@BrentL You actually do need a Calendar Table!

It seems it works without Calendar table only when the you are using DAY aggregation

Its better to have one in your model anyway - all time intelligence functions require one! (which actually these are part of)

https://msdn.microsoft.com/en-us/library/ee634763.aspx

Here's a link provided by Matt on how to create one

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

This should take care of it - let me know if it doesn't.

[BrentL]

@Sean Thank you so much for all the help.  I had started making the calendar table as you sent this last reply and I got it to work.  I have saved those links to my favorites so I can review and study them.  It is working great now!!  Much appreciation.

[BrentL]

Hello there. I am sorry I am not getting it. Everything is within the same receipt table. The date is the received date from the same table. I can modify and upload the table if that would help.

[Beckster]

Thanks @Sean! With a little tweaking this worked great!

[Anonymous]

Hi @Beckster @BrentL and @Sean,

I'm also having the same issue.

I've created a calender as per the instruction post by Sean but it didn't work for me.

Appriciate if one of you guys can help me.

Below is my formula,

Running Total = CALCULATE([TRI],DATESINPERIOD(Calender[Date],LASTDATE(Calender[Date]),-12,MONTH))

Regards,

Thilan