DAX for the average of 5 latest orders by the retailers based on the Order dates.

[pranshu_07]

Hello all,

I've to calculate the average of the last 5 orders placed by the retailers. When I use quick measure (average per category) it shows right result for the retailers <=5 but for the retailers above 5 orders it calculate the total order average. 

Average_order = AVERAGEX ( KEEPFILTERS(VALUES('Catalog Line Item'[Order_Date])),

    CALCULATE(SUM('Catalog Line Item'[Order_value]))

)

I modified the dax for the last 5 result using top N function,

Average_Order =
AVERAGEX(
KEEPFILTERS(TOPN(5,'Catalog Line Item','Catalog Line Item'[Order_Date].[Date],DESC)),
CALCULATE(SUM('Catalog Line Item'[Order_value]))
)

The results were wrong,
Kindly request your assistance.

[amitchandak]

@pranshu_07 , if this not based on selected date, then you can have column rank

example

rankx(filter(Table, [retailer_ID] = earlier([retailer_ID] )), [Order_Date],,desc,dense)

and filter <=5

[shri0025]

Hi I Need Last 5 order Averge Sales on Outlet based . SO can you elaborate on the above DAX that after alocatting rank to the date of sale how can I calculate the average.

for eg: Condition 1: If a retailer buy 3000 rs. worth of item only 3 times this year than the average will be 3000/3=1000.

but if the other retailer had bought 3000rs. worth items 5 times this year then it should be 600.

and if he bought more than 5 times than also 600.

the sales may be continous or in a random manner like (jan, march,august) 

[achanikya]

@amitchandak 

[amitchandak · Accepted solution]

@achanikya , Please, check my code again

rankx(filter(Table, [retailer_ID] = earlier([retailer_ID] )), [Order_Date],,desc,dense)