topic Re: Average from last 30 results in DAX Commands and Tips

Average from last 30 results

Pan_Forex — Mon, 28 Nov 2022 14:17:51 GMT

Hello everyone,

This is my first post and the beginning of my adventure with dax so please be understanding. I have a table with 3 columns: index, score and player. I try to count the average score of the last 30 indexes for each player. I have over 14,000 lines and over 300 players so for each player the last 30 indexes will be different.

Thank you for all the great content you have here - it is a great place to learn 🙂

Re: Average from last 30 results

amitchandak — Mon, 28 Nov 2022 15:21:39 GMT

Pan_Forex , Create an index column in table using DAX

index= rankx(filter(Table, [Player] = earlier([Player]) ), [id],,asc,desc)

Create a new table

series = generateseries(Min(Table[index]), Max(Table[index]) ,1)

then create join series[value] with rank and create a measure

calculate(average(Table[game_score]), filter(all(series) , series[Value] >= max(series[Value]) -30 && series[Value] <= max(series[Value]) ))

Re: Average from last 30 results

DimaMD — Mon, 28 Nov 2022 15:21:50 GMT

Hi, Pan_Forex Can you provide sample data with sensitive data removed

Re: Average from last 30 results

Pan_Forex — Mon, 28 Nov 2022 15:48:06 GMT

Hi, thank you very much for your reply! When creating an index column, it displays an error: Unexpected value of the TIES argument in the RANKX function. The allowed values are SKIP and DENSE.

My function looks like this:

index = rankx(FILTER('public players', [Player] = earlier([Player]) ), [id],,asc,desc)

Re: Average from last 30 results

Pan_Forex — Mon, 28 Nov 2022 22:19:15 GMT

Hello, yea sure 🙂 here you will find everything https://we.tl/t-X27MhOsCQD

Re: Average from last 30 results

Pan_Forex — Tue, 29 Nov 2022 13:53:09 GMT

I fixed the errors, corrected the relationships and everything works. Thank you very much!