Running Average Scores By User By Week

Hi Anonymous,

A bit hard work way. Please refer to the steps as below.

1. Create two calculate tables.

date = VALUES('Table1'[WeekStarting])

id = VALUES('Table1'[UserId])

2. Create a rank column in table date.

Column = RANKX(ALL('date'),'date'[WeekStarting1])

3. Cross join the two tables as a new one. And create two calculated column in the new table.

newjoin = CROSSJOIN('date','id')

result = LOOKUPVALUE(Table1[Score],Table1[WeekStarting],newjoin[WeekStarting1],Table1[UserId],newjoin[UserId])

newScore = var new = newjoin[rank]+1
var new1= newjoin[rank]+2
var re = CALCULATE(SUM(newjoin[result]),FILTER(ALLEXCEPT(newjoin,newjoin[UserId]),newjoin[rank]=new))
var re1 = CALCULATE(SUM(newjoin[result]),FILTER(ALLEXCEPT(newjoin,newjoin[UserId]),newjoin[rank]=new1))
var sc = IF(ISBLANK(newjoin[result]),re,newjoin[result])
return
IF(ISBLANK(sc),re1,sc)

4. To create the measures as below to get the result as you need.

DistinctUserCount = CALCULATE(DISTINCTCOUNT(newjoin[UserId]),FILTER(newjoin,newjoin[newScore]<>BLANK()))

AverageScore1 = CALCULATE(SUM(newjoin[newScore]))/[DistinctUserCount]

Please find the pbix as attached.

Regards,

Frank