Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hi BI and DAX gurus,
All the moving average numbers of each month you see are actually belong to the previous month (The moving average curve line started from Feb 2023 than the beginning of aixs which is Jan 2023). I don't know why the moving average curve line shift itself forward for 1 month. i.e. The number (1.0) showing against Feb 2023 belongs to Jan 2023 which mean the entire bar chart is mismatched between moving average curve line and the date X axis columns by 1 month.
I tried millions of way to tweak the formula but still couldn't resolve it - wonder if anyone has answer/expereince to this issue ?
manual data table code used:
moving average DAX code used:
Solved! Go to Solution.
PBI file attached.
Hope this helps.
Hi,
Share some data to work with, explain the question and show the expected result in a Table format. Share data in a format that can be pasted in an MS Excel file.
hi Ashish
All I am trying to achieve is make the moving average line move left for 1 month that will solve everything.
Just give you some context: the Y axis shows the monthly count number of injury reports filtered by 'serious injury' and I am trying to work out the moving average of 'serious injury' by month.
all the numbers showing on moving average curver you see are actually correct - it just mismatching its month by one month late
Share the download link of the PBI file.
Access denied message.
my apology Ashish link permission has been updated should work now link to 'moving average pbix file'
outstanding! I actally almost solved it at the same time by making the below change and it worked as well! see the below screenshot - I basically replaced 'MIN' with 'MAX' and it just worked
Thank you.
@lovecats , Try measure like
12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
Rolling 12 Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value]))), WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc))) )
You might have put additional control to stop on the available date
if(Max(date[Date]) <= maxx(all(Table), Table[Date]) , [Rolling 12 Avg], blank())
hi Amit thank you
I tried adding your code to a measure however the syntax 'WINDOW' can't be recognised by BI - is this standard DAX code?
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |