Forum Discussion

Vinod_P's avatar
Vinod_P
Helper 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. 

3 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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.

  • Vinod_P's avatar
    Vinod_P
    Helper 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
  • Vinod_P's avatar
    Vinod_P
    Helper I
    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.