cancel
Showing results for
Did you mean:
Frequent Visitor

## Moving average unable to calcualte

Dear Friends,

I am new to Power BI  community and recently started to use this wonderful program.

However I am stuck with one calculation.

I already have a coloumn in table with Numbers of customer visits per shop per month (shop by shop data is available)

Now I need to calculate 12 month moving average.

I already made calendar table and created connection, however, when I use formula i.e Average X & Dates in period combination or Sum function etc., then it is taking from Dec-2020 since calendar last day in Dec-2020 due to another table

6 REPLIES 6
Super User III

Hi @ngour615 ,

Can you share sample data and your data model and also the expected output.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Frequent Visitor

1) Source file (big file with many coloumn: deleted few coloumn in make easy for you to understand):

2) Current data model. Final goal is a many graph. However, in 1 graph which is related to 12 month moving average I stuck up. Its source is only DPR

3) Currently we are doing the same job in excel file. Every month 1 staff is putting all data in excel as below (it takes 1 full day to make it) (now I loaded all in power querry/BI) and will make automatic dashboard.

4) Output graph is attached: All values are MOVING average of 12M from 3) source

Nikhil.

Solution Sage
``````// Your Calendar table should
// have all days covering all
// full years that are present
// in your data. Then, you should
// connect the field [Date] to
// the field of THE SAME TYPE in
// your fact table. The fact that
// you collect data monthly has nothing
// to do with the fact that you'll
// be joining the two tables on a date,
// not on a month. In the fact table
// you just mark a month as the first
// date of the relevant month and join on it
// to the calendar. Then, if you don't
// really deal with days, you should
// hide days and only leave the relevant
// pieces of time exposed (months, years...).
// Of course, Calendar must be marked as
// a date table in the model. The code
// below will work on assumption that
// you've hidden days in the calendar.

// Once you have the above, moving
// avg is easy to create.

[Num Of Visits - 12M Avg] =
// If you want to see the avg per shop,
// just drop shops on the canvas.
var __lastVisibleDate = MAX( Calendar[Date] )
var __veryFirstYearInCalendar =
CALCULATETABLE(
MIN( Calendar[Year] ),
ALL( 'Calendar' )
)
var __lastEffectiveDateInFirstYear =
// Only if the last visible date is
// on or after this date, you're able
// to calculate the 12M average. Otherwise,
// there will not be enough months to
// average over.
date( __veryFirstYearInCalendar, 12, 1 )
var __canCalculate =
__lastVisibleDate >= __lastEffectiveDateInFirstYear
return
if( __canCalculate,

var __monthsToAvgOver =
CALCULATETABLE(
// UniqueMonthId must be a unique int
// assigned to each unique month. No
// two months across the entire calendar
// can have the same ids.
VALUES( Calendar[UniqueMonthId] ),
DATESINPERIOD(
Calendar[Date],
__lastVisibleDate,
-12,
MONTH
)
)
return
AVERAGEX(
__monthsToAvgOver,
CALCULATE(
SUM( FactTable[Number Of Visits] ),
ALLEXCEPT(
'Calendar',
'Calendar'[UniqueMonthId]
)
)
)
)``````

If you also deal with single days, you should wrap this measure in a guard clause that will calculate this only when full months are visible, not just dates or a selection of dates that does not cover full months.

Frequent Visitor

Hello,

Thanks for suggestions.

1) Calendar table contains all dates - Yes. it is correct.

2) Relation with Calendar table is made

3) Thanks for providing insights for DAX. I will try and confirm

Frequent Visitor
Frequent Visitor

Announcements