cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsch001 Frequent Visitor
Frequent Visitor

DAX formula help - raw data available with intended result set

The raw data is a combination of 3 dimensions (User, Calendar which are dates with a start of week computed, and ExamType) and 1 fact table with the scoring information.

RawData:   
     
UserDateWeekOfExamScore
Tim1/2/201912/31/2018Baseline75
Ellie1/3/201912/31/2018Baseline90
Jon1/2/201912/31/2018Baseline88
Ellie1/10/20191/7/2019Update91
Tim1/10/20191/7/2019Update90
Ellie1/21/20191/21/2019Update95
Sophia1/21/20191/21/2019Baseline63
Sophia1/29/20191/28/2019Update77

 

 

Below is what I'm hoping for in a result set from the DAX formula, the AverageScore can be a measure created later... I'm looking for Scores and Users by week. Each User will take a "Baseline" ExamType and get a score, after they get their intial baseline score, they can update it by taking an "Update" ExamType and changing their score. I care the latest ExamType for each user within the Week context. 

 

Intended DAX Result Set:  
    
Week OfScores (numerator)Users (denominator)AverageScore
12/31/2018253384.33333333
1/7/2019269389.66666667
1/14/2019269389.66666667
1/21/2019336484
1/28/2019350487.5

 

Description of what the result set is showing us:

 

Week OfDescription of Activity
12/31/2018Tim, Ellie, Jon take baseline exam (we sum their scores and divide by the 3 users to get average)
1/7/2019Tim and Ellie take update exam and change score from baseline (we sum Tim/Ellies Update and Jons baseline, divide by 3)
1/14/2019no activity, same as prior week
1/21/2019Ellie takes update exam and changes scores, Sophia takes baseline exam, User count increases (we take Ellies latest Update exam score, Tims Update score, Jon and Sophias baseline exam scores because they don't have an update available)
1/28/2019Sophia takes update exam and changes score, user count stays

 

 

Any help is greatly appreciated, this has been a brain buster for me for awhile!