Average of Sum with a filter for a prior period

[dphillips]

Students are rated on their LP (Learner Profile) in 6 LP areas per subject for each reporting period (Result Group). Each LP is marked out of 4 so a student could have a maximum mark of 24 per subject they do. I have used the following measure to get a long term average of the sum of their LPs. (Black line in image)

LP Long Term Ave = CALCULATE(
AVERAGEX(
SUMMARIZE(LPData,LPData[ResultGroup],
LPData[StudentID],LPData[AssessmentCode],
"Average by Class",SUM(LPData[LPResult])),[Average by Class]),
ALLEXCEPT(LPData,LPData[CohortYear],LPData[StudentID]))

This seems to work correctly. I also have the Ave LP out of 24 for each Result Group for an individual student. (Blue line in image)

LP Ave by Result Group = AVERAGEX(
SUMMARIZE(LPData,LPData[ResultGroup],
LPData[AssessmentCode],
"Average by Class",sum(LPData[LPResult])),[Average by Class])

What I need is to get the long term average LP out of 24 for a student for every result group PRIOR to the Result Group selected in the slicer. I have tried the following:

LP Long Term Ave excluding current Result Group = 
var ResultGroupNumber = calculate(max(LPData[ResultGroupOrder]),allexcept(LPData,LPData[ResultGroup]))
RETURN
    calculate(
             AVERAGEX(
                    SUMMARIZE(filter(LPData,LPData[ResultGroupOrder] < ResultGroupNumber),
                                    LPData[ResultGroup],
                                    LPData[StudentID],
                                    LPData[AssessmentCode],
                                    "Average by Class", SUM(LPData[LPResult])),[Average by Class]),
                                    allexcept(LPData,LPData[StudentID]))

As you can see I have used a variable to get the value of the currently selected result group. I then filter the LPData by asking for any results that are less than that Result Group number. I want a horizontal line with the same value for every result group and for student 121939 I would expect a value of 17.9. Instead I get the red line shown in the image where the value changes over the result groups. This is not what I want - I want a horizontal line with just the one value all the way across.

In the end, I want to be able to see the difference between a students current LP average for this reporting period compared to their long term average for the reporting periods PRIOR to the currently selected. I don't want what they got for this reporting period to be included in the long term average. this needs to be dynamic so as a user selects different reporting periods it will only include data from periods prior to that period.

See image 

 Here is the link to the report - https://drive.google.com/file/d/1ssGa0hP_bomOjFOuKdppM8vq8tRU1MG7/view?usp=sharing

Hope someone is able to help.

[amitchandak · Accepted solution]

@dphillips , I am able to the meaning of the prior period here. If it is nonstandard period comparison, you can choose rank way of week.

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

[dphillips]

The last link here seemed to offer a solution which I have worked through. In the end I used the following measure.

LP Average Previous Report Groups = 

VAR CurrentResNumber = selectedvalue(uncRedshift_Studentresults[Result Number])

RETURN
calculate(
    AVERAGEX(
    uncRedshift_Studentresults,[LPTotByClass]),
    FILTER(ALLEXCEPT(uncRedshift_Studentresults,uncRedshift_Studentresults[NameNum]),
    uncRedshift_Studentresults[Result Number] < CurrentResNumber &&
    uncRedshift_Studentresults[ResultType] = "LPResults" &&
    uncRedshift_Studentresults[Result Group] <> "TR_Yr7")
)

The important thing is that I did not use it in a line graph over time. The red line in the graphs above is right but it will not be the same at different points - it is constantly changing based on the new values from one reporting period to another. so I have just used the measure above, compare the value for this reporting period:

LP % Change Current to Previous = DIVIDE([LP Ave Current Report Period] - [LP Average Previous Report Groups],[LP Average Previous Report Groups])*100

and graph this value - this then gives me the students with the greatest % change from their previous average long term LP.

SELECTEDVALUE was an important DAX function which elped in making this work.

[dphillips]

Thanks for your response. At the moment my data is not associated with specific dates other than something like "2019 Term 2" report results. Do you have any advice on adding a date calendar that would help me achieve the sort of thing you have sent links to?