Average of Sales for the last 25 orders

[asadighim]

Hello, 

I am trying to find average of sales in dollar amount for the last 25 orders placed, as of the date of the order. 

I have the date of each order and the dollar amount associated with them. 

I want to know the average of the last 25 transactions (y-axis)

and then display it by the the date of the latest of the last 25 transactions (x-axis). 

My last 25 transactions could be a window of 5 days to 6 months.

I appreciate any help here. 

[v-lid-msft · Accepted solution]

Hi @asadighim ,

We can create a measure use following formlua as y-axis to meet your requirement:

Average = 
VAR t =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "index", RANKX ( ALLSELECTED ( 'Table' ), [Date],, ASC )
    )
VAR currentday =
    RANKX (
        ALLSELECTED ( 'Table' ),
        [Date],
        CALCULATE ( MAX ( 'Calendar'[Date] ) ),
        ASC
    )
RETURN
    IF (
        currentday
            >= MAXX ( t, [index] ) - 24,
        AVERAGEX (
            FILTER ( t, [index] >= currentday - 24 && [index] <= currentday ),
            [Value]
        ),
        BLANK ()
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[asadighim]

Hey @v-lid-msft 
Thank you very much! This worked beautifully! 

[amitchandak]

Calculate Rank on the date in desc order and filter for rank. Or use transaction id if available

Refer

https://community.powerbi.com/t5/Desktop/DAX-Ranking-Date/td-p/98237

https://community.powerbi.com/t5/Desktop/Rank-Date/td-p/326154

https://community.powerbi.com/t5/Desktop/Rank-based-on-date-for-each-individual-user/td-p/411053

https://community.powerbi.com/t5/Desktop/Rank-by-Dates-and-Customer-ID/td-p/118837

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601