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 not all. 

 

Please can someone help me with troubleshooting this. 

 

The idea is the user can select a number of days (Purchase Date) to average over which can be built into a slicer. Technically I want it to be a number of datapoints rather than days (so it will ignore weekends and bank hols where there are no prices for example). 

 

Some added complication - the average should be calculated for each fruit and for each "Delivery Date" which should be able to aggregate from months >quarters > seasons >years. Purchase date determines the average period, delivery date can be seen as a different category. 

I've highlighted an example where it starts to go wrong compared to manually calculating this in excel. 

 

 

The DAX is below:

 

0.Moving Avg =


Var Current_Date = FIRSTNONBLANK('Fruit Data'[Purchase Date],1)
Var Number_Of_Days = SELECTEDVALUE('Average Days'[Average Days])
Var Offset_Date = filter(
ALL('Calendar'[Date]),
RANKx(FILTER(ALL('Calendar'),'Calendar'[Date]<(Current_Date-1)),'Calendar'[Date],,DESC)=Number_Of_Days)

VAR avrg =
CALCULATE( AVERAGE('Fruit Data'[Price]),
FILTER ( ALL ('Fruit Data'[Purchase Date]), 'Fruit Data'[Purchase Date]< Current_Date && 'Fruit Data'[Purchase Date] > Offset_Date))

return avrg
 
 
 

 

 

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.

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your help here is the raw data and an example of how I want the calculation to work:


       Fruit Dataset 


      In terms of how I'd like it to look in PowerBI, I would need to plot the data in both a table and on a graph where for each Purchase Data, and each Delivery Date there would be a dynamically calculated rolling average that could be 5,10,15,20 days (etc). I should also be able to aggregate up the delivery date to a delivery month or delivery year and the average should still calculate. In the example in the sheet the delivery period is Summer 2024.


      As long as the average calculates correctly in a table I can figure out the rest from there though.

       Fruit Dataset 

  • 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.