Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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

@achanikya , Please, check my code again

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

View solution in original post

Super User
Super User

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



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


and filter <=5

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 , Please, check my code again

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

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors