Query on rolling moving average

[Vinod_P]

Created a column Yard(Difference of 2 dates) and now want to create rolling moving average for the Yard column but DAX not showing Yard column. Need a wayout 

[Vinod_P · Accepted solution]

I tried rolling average from quick measure and it worked well but I am having data after the current year and need solution to stop rolling average to current month. 

[Vinod_P]

Looking to find last 12 months moving average, ideally from march 2022 to feb 2023  

ID NUMBER	PO DATE	GOODS RECIEPT	EST. OUT MONTH	Ref Date	Prep Start Date	Yard	Yard Rolling 12 months
18PI15266	18/08/2021	28/09/2018	2022-01	Ref Date = IF('N3M Curr'[PO DATE].[Date]<> BLANK() && 'N3M Curr'[GOODS RECIEPT].[Date]<> BLANK(),  MAX('N3M Curr'[PO DATE].[Date],'N3M Curr'[GOODS RECIEPT].[Date]),  MAX('N3M Curr'[PO DATE].[Date],'N3M Curr'[GOODS RECIEPT].[Date])	Prep Start Date = CALCULATE(FIRSTNONBLANK(FSAT[Start Date].[Date],1),FILTER(ALL(FSAT),'N3M Curr'[ID NUMBER]=FSAT[Equipment ID]))	Yard =     IF('N3M Curr'[Prep Start Date].[Date]<> BLANK()     && 'N3M Curr'[Ref Date].[Date] <> BLANK()     && 'N3M Curr'[Prep Start Date].[Date]>= 'N3M Curr'[Ref Date].[Date],         'N3M Curr'[Prep Start Date].[Date]-'N3M Curr'[Ref Date].[Date],         0     )	YARD R12M =     var no.of.months = 12     var maxdate = MAX('N3M Curr'[EST. OUT MONTH].[Date])     var Period =         DATESINPERIOD('N3M Curr'[EST. OUT MONTH].[Date],maxdate,no.of.months,MONTH)     var Result =         CALCULATE(             AVERAGEX(                 VALUES('N3M Curr'[EST. OUT MONTH].[Date]),                 ('N3M Curr'[Yard])             ),             Period         )         Return          Result

[v-jingzhang]

Hi @Vinod_P 

It seems your current Yard column doesn't return the correct date difference result, right? If I understand it correctly, you may try the following DAX for the Yard column. 

Yard =
IF (
    'N3M Curr'[Prep Start Date] <> BLANK ()
        && 'N3M Curr'[Ref Date] <> BLANK ()
        && 'N3M Curr'[Prep Start Date].[Date] > 'N3M Curr'[Ref Date].[Date],
    'N3M Curr'[Prep Start Date].[Date] - 'N3M Curr'[Ref Date].[Date],
    0
)

If it doesn't work, can you provide some sample data and expected result in table format? And for the rolling moving average, it should roll by what and calculate the average in which moving period? Using some sample data will help us understand it better. How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.