Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @Anonymous ,
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)
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |