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.
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 Of||Scores (numerator)||Users (denominator)||AverageScore|
Description of what the result set is showing us:
|Week Of||Description of Activity|
|12/31/2018||Tim, Ellie, Jon take baseline exam (we sum their scores and divide by the 3 users to get average)|
|1/7/2019||Tim and Ellie take update exam and change score from baseline (we sum Tim/Ellies Update and Jons baseline, divide by 3)|
|1/14/2019||no activity, same as prior week|
|1/21/2019||Ellie 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/2019||Sophia takes update exam and changes score, user count stays|
Any help is greatly appreciated, this has been a brain buster for me for awhile!