Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Moving Average Using an Index Not Working

Fruit Dataset   EDIT - INCLUDED RAW DATASET AND EXAMPLE OUTCOME   Hi, I'm working on a dynamic moving average for the prices of fruit. I've written a measure that works correctly in some cases but ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your new sample, here's my solution.

    Create two measures.

    Avg of Price = 
CALCULATE (
    AVERAGE ( 'Fruit Data'[Price] ),
    FILTER (
        ALL ( 'Fruit Data' ),
        'Fruit Data'[Purchase Date] = MAX ( 'Fruit Data'[Purchase Date] )
            && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
            && 'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
    )
)

    Moving Avg = 
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    AVERAGEX (
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        ),
        [Avg of Price]
    )
RETURN
    avrg

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

Hi Anonymous ,

In your below formula, you should not use "Current_Date-1", as the Purchas Date is not continuous.

FILTER(ALL('Calendar'),'Calendar'[Date]<(Current_Date-1))

Here's my solution.

1.The formula of Calendar table.

Calendar = VALUES('Fruit Data'[Purchase Date])

2.The formula of Moving Avg measure.

Moving Avg =
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    CALCULATE (
        AVERAGE ( 'Fruit Data'[Price] ),
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery Date] = MAX ( 'Fruit Data'[Delivery Date] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        )
    )
RETURN
    avrg

 Get the result.

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  thanks so much for looking at my problem, I really appreciate the help! 

    I've tested this on an example where we are looking at Grapes for the Delivery FT 2023. I think the problem is where we need the single Delivery Date averages to be aggregated to one delivery FY.

     

    I've created a new sheet here Fruit Prices Model Test  which I think demonstrated the problem and expected outcome if you look in the "Grapes Test Sample" tab. I've screenshotted the output your model gives under these conditions vs the excel calcs. 

     

     

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      4 years ago

      Hi Anonymous ,

      According to your new sample, here's my solution.

      Create two measures.

      Avg of Price = 
CALCULATE (
    AVERAGE ( 'Fruit Data'[Price] ),
    FILTER (
        ALL ( 'Fruit Data' ),
        'Fruit Data'[Purchase Date] = MAX ( 'Fruit Data'[Purchase Date] )
            && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
            && 'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
    )
)

      Moving Avg = 
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    AVERAGEX (
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        ),
        [Avg of Price]
    )
RETURN
    avrg

      Get the expected result.

      I attach my sample below for reference.

       

      Best Regards,
      Community Support Team _ kalyj

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thank you so much!! Awesome it finally works.