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
Hi @ngour615 ,
Can you share sample data and your data model and also the expected output.
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
Thanks for your response.
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
// 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.
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
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.