Search for the value that was at the beginning of the filtered year

[nok]

Hi!

I have two tables that follow this structure:

"Main"

Entity	FileDate
AAA	12/09/2025
BBB	12/03/2025

"Responses"

Entity	Last Response Date	Score
AAA	02/02/2024	15
AAA	30/06/2024	20
AAA	20/02/2025	50
AAA	29/08/2025	40
BBB	02/02/2023	32
BBB	28/03/2025	45

I want to create a measure that search the Score value from "Responses" table, relating the two tables by the Entity column. But it needs to bring the score that was at the beginning of the year, that is, the score that was when the day was January 1st of the year that the user filtered (based on the "FileDate" column of the "Main" table). If there is no previous value for the filtered year, the calculation should return the most recent previous score.

Example 1: If the user filtered on today's date, 30/09/2025, the final result should be this:

Entity	Q1_Score
AAA	20
BBB	32

PS: The AAA score is 20, as that was the score on 01/01/2025. Only on 20/02/2025, the score changed to 50. Therefore, the correct value to be displayed for this score is 20.

PS.2: The BBB score is 32 because, even though it is the score defined in 2023, it remained the same until it was changed in March 2025. As on 01/01/2025 it was still 32, this is the correct value.

Example 2: If the user filtered on the date of the 12/12/2024, the final result should be this:

Entity	Q1_Score
AAA	15
BBB	32

How can I do this?

[danextian]

Hi @nok 

I am confused. I don't get the significance of the file date. The user selects Sept 30 which isnt in the file dates. The way I see it is you're looking for the the value of  the earliest date before the selected date. 

[Ashish_Mathur]

HI,

What is the use of the Main table in asnwerig your question.

[GeraldGEmerick · Accepted solution]

@nok I believe that the following should be sufficient for your use case:

Previous Score = 
VAR _Date = MAX( 'Main'[FileDate] )
VAR _PreviousDate = CALCULATE( MAX( 'Responses'[Last Response Date] ), 'Responses'[Last Response Date] < _Date )
VAR _Return = CALCULATE( MAX( 'Responses'[Score] ), 'Responses'[Last Response Date] = _PreviousDate )
RETURN _Return

[lbendlin]

If there is no previous value for the filtered year, the calculation should return the most recent previous score.

That means your example 2 should yield 20 for AAA, not 15.

Please clarify "most recent previous score".