why my moving average/rolling average curve line showing the number of the previous month?

[lovecats]

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:

FF calendar = ADDCOLUMNS(CALENDARAUTO(),"year", YEAR([Date]), "month", EOMONTH([Date], -1) +1, "qtr", "Q" & FORMAT([Date], "q"), "YearQtr", YEAR([Date]) & " Q" & FORMAT([date], "q"))

moving average DAX code used: 

FF Moving Average =

VAR _CurrentDate =

MIN ( 'FF calendar'[Date] ) +1

VAR _FilterDate =

DATESINPERIOD ( 'FF calendar'[Date], _CurrentDate, -12, MONTH )

VAR _Monthly =

CALCULATE ('Occurrence Monitor'[TOTAL MAPPING ID], _FilterDate )

VAR _MonthCount =

CALCULATE ( DISTINCTCOUNT ( 'FF calendar'[month] ), _FilterDate )

VAR _Average =

DIVIDE ( _Monthly, _MonthCount )

RETURN

_Average

[Ashish_Mathur]

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.

[lovecats]

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

[Ashish_Mathur]

Share the download link of the PBI file.

[lovecats]

link to my PBIX file  here it is let me know if you having trouble access

[Ashish_Mathur]

Access denied message.

[lovecats]

my apology Ashish link permission has been updated should work now link to 'moving average pbix file' 

[Ashish_Mathur · Accepted solution]

PBI file attached.

Hope this helps.

[lovecats]

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

[Ashish_Mathur]

Thank you.

[amitchandak]

@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())

[lovecats]

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?