Returning the latest value by date, if no value for date period return the previous value

[Jtbonner1986]

Hello 

I have a data set and I have a calendar table. I want to plot the last price paid by calendar month. If there is no data for that month I want the previous months value etc etc. 

Example of my Data Set:

Material	Date	Cost
123	01/06/2024	£100
123	01/05/2024	£85
123	10/05/2024	£95
123	01/03/2024	£85
123	28/03/2024	£90
123	01/01/2024	£50

The result i want to see is:

Material	Date	Last Price Paid
123	Jan-2024	£50
123	Feb-2024	£50
123	Mar-2024	£90
123	Apr-2024	£90
123	May-2024	£95
123	Jun-2024	£100

Struggling to plot where there are gaps in the dataset on a visual, trying to use the calendar table 

please help

[johnt75 · Accepted solution]

If you have a maximum of 1 entry per material per date then the below should work I think.

Last Price Paid =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Cost] ),
        'Date'[Date] <= MaxDate
    )
VAR Result =
    SELECTCOLUMNS (
        TOPN ( 1, BaseTable, 'Table'[Date], DESC ),
        "@value", 'Table'[Cost]
    )
RETURN
    Result