Moving Average Using an Index Not Working

[Anonymous]

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

[v-yanjiang-msft]

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]

Hi v-kalyj-msft 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 · Accepted solution]

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]

Thank you so much!! Awesome it finally works.

[lbendlin]

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]

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