How to take last data available based on the slicer?

Question

I got a request for a visual to take the last data available based on the slicer. We have year, quarter and month slicers in the report. If multiple months are selected, there will be no summarization, but the latest data available should be present in the chart. Take a look at the sample data in the image.

Site    Assessment date Score
71      3/31/2023       4.05
71      6/23/2023       4.2
71      9/14/2023       3.256
71      12/20/2023      5

As you can see, there are gaps in the table. There is not available score for each month. However, I need to make such DAX, that even if the user selects only November, the visual will show the last available data, which in this case will be **September **score of 3.256 .

I am not sure how to proceed here. Any help or suggestions would be greatly appreciated.

Answer 1

First create a Date table :

Date = CALENDAR("2023-01-01", "2023-12-31")

and then link it to the main table :

Last Available Score  = 
VAR SelectedMaxDate = 
    MAX('Date'[Date]) 
VAR LastAvailableScoreDate =
    CALCULATE(
        MAX('ScoresTable'[Assessment date]),
        ALL('ScoresTable'), 
        'ScoresTable'[Assessment date] <= SelectedMaxDate
    )
RETURN
    IF(
        ISBLANK(LastAvailableScoreDate),
        BLANK(), // return blank if there's no data at all before the selected date
        CALCULATE(
            MAX('ScoresTable'[Score]),
            ALL('ScoresTable'), // Ignore the slicer to get the score for the last available date.
            'ScoresTable'[Assessment date] = LastAvailableScoreDate
        )
    )

You need to find the latest date selected in the slicer through SelectedMaxDate and then determine the last available assessment date (LastAvailableScoreDate) up to the selected date to capture the latest possible data point.

Here is an example when I select a random date in November :

Same for August :