Forum Discussion

Vinod_P's avatar
Vinod_P
Icon for Helper I rankHelper I
3 years ago
Solved

Query on rolling moving average

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   
Show and Tell
  • Vinod_P's avatar
    Vinod_P
    3 years ago

    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's avatar
Vinod_P
Icon for Helper I rankHelper I
3 years ago

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

ID NUMBERPO DATEGOODS RECIEPTEST. OUT MONTHRef DatePrep Start DateYardYard Rolling 12 months
18PI1526618/08/202128/09/20182022-01Ref 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