Prior Row Calculation in Measure

[Anonymous]

I have two tables:

1. Student. This table joins to the Exam table on [id], 1:*
2. Exam.

I am trying to create a measure to calculate the average datediff between each students exam dates.  Unfortunately, I am struggling to create a table for AVERAGEX to iterate over because I cannot create a "Prior_Exam_Date" column.  See my progress below:

Average Days Between Exams:=
AVERAGEX (
    SUMMARIZECOLUMNS (
        Student[id],
        Exam[Exam_Name],
        Exam[Exam_Date],
        "Prior_Exam_Date", ??? // this measure should return the most recent available Exam[Exam_Date] for the current Student[id]. 
    ),
    DATEDIFF ( [Prior_Exam_Date], [Exam_Date], DAY )
)

I understand that I can easily add a physical "Prior_Exam_Date" column to my dataset to accomplish this however in this case I must calculate it on the fly.  Can you please help me fill in the ??? above?

Thanks,
Simon

[v-haibl-msft · Accepted solution]

@Anonymous

Without using calculated column, I create some measures to get the expected result in attached sample file.

Prior_Exam_Date = 
CALCULATE (
    MAX ( Exam[Exam_Date] ),
    FILTER (
        ALLEXCEPT ( Exam, Exam[id] ),
        Exam[Exam_Date] < MAX ( Exam[Exam_Date] )
    )
)

DayDiff = 
DATEDIFF ( [Prior_Exam_Date], CALCULATE ( MAX ( Exam[Exam_Date] ) ), DAY )

Average_DayDiff = 
AVERAGEX ( ALLEXCEPT ( Exam, Exam[id] ), [DayDiff] )

Best Regards,
Herbert

[Interkoubess]

Hi @Anonymous,

Could you please send ( share)  dummy data with expected results and then we can test different approaches.

Thank you.

Ninter