cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Community Champion

@Beckster Let me know if these work for you.

11 REPLIES 11
Community Champion

@Beckster Let me know if these work for you.

Frequent Visitor

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,

Community Champion

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

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

Frequent Visitor

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,

Community Champion

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

Frequent Visitor

@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.

Community Champion

@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.

Frequent Visitor

@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.

Frequent Visitor
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.
Frequent Visitor

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

Anonymous
Not applicable

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors