cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ngour615
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.

Here I am stuck up and need your advise

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

 

Pls. advise

 

6 REPLIES 6
harshnathani
Super User III
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)

 

Hi @harshnathani 

Thanks for your response.

 

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

Base source file excel.PNG

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

Data model.PNG

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.

current staff way in excel putting all values in excel and using simple average(range) formula.PNG

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

  

expected output MOVING AVERAGE 12 MONTH.PNG

 

Nikhil.

 

 

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

ngour615
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

ngour615
Frequent Visitor

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors