DAX formula help - raw data available with intended result set

[Anonymous]

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:
User	Date	WeekOf	Exam	Score
Tim	1/2/2019	12/31/2018	Baseline	75
Ellie	1/3/2019	12/31/2018	Baseline	90
Jon	1/2/2019	12/31/2018	Baseline	88
Ellie	1/10/2019	1/7/2019	Update	91
Tim	1/10/2019	1/7/2019	Update	90
Ellie	1/21/2019	1/21/2019	Update	95
Sophia	1/21/2019	1/21/2019	Baseline	63
Sophia	1/29/2019	1/28/2019	Update	77

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
12/31/2018	253	3	84.33333333
1/7/2019	269	3	89.66666667
1/14/2019	269	3	89.66666667
1/21/2019	336	4	84
1/28/2019	350	4	87.5

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!

[smpa01]

@AnonymousI think that I have what you may need.

I was succesful in feeding your logic to come up with a measure that makes the appropriate numbers of rows (all rows with WeekOf <= curent filter context WeekOf date) visible to the current filter context (WeekOf) for the calculation

Numerator :=
VAR _1 =
    MAX ( 'Table'[WeekOf] )
VAR _3 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ),
        [User],
        "X", MAX ( [WeekOf] ),
        "Y", MAX ( [Score] )
    )
VAR _5 =
    SUMX ( _3, [Y] )
RETURN
    _5

Denominator := 
VAR _1 =
    MAX ( 'Table'[WeekOf] )
VAR _3 =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[WeekOf] <= _1 ),
        [User],
        "X", MAX ( [WeekOf] ),
        "Y", MAX ( [Score] )
    )
VAR _5 =
    COUNTX( _3, [Y] )
RETURN
    _5

[GilbertQ]

Hi @Anonymous 

Please find the PBIX.

Based on your source data I have created the Intended DAX results.

What I did was to create the 3 measures as detailed in the PBIX.

I then used the column called WeekOf to get it showing the values per week.

If there are any additional questions please let me know.