Get last record prior to selected in slicers

[Gekko1]

So, I'm currently having an issue as described below:

• I have this table (let's call it Table X) where I have the company ID, which is related to the companies table by the same field (ID);
• There is also the product ID, which is related to products table by the ID field too;
• There is a field which is the amount of the product and the date that amount was "measured"

Company	Product	Amount	Date
1	15	1500	2022-01-01
1	16	2000	2022-02-01
2	15	2000	2022-01-15
2	16	3000	2022-02-15

With that in mind, I need a measure which gets the product amount on the last date available. Example, if I select the february month on slicers it should show the amount 2000 (since there's no data for february, it gets the last date prior to february) for the product 15 and 3000 for the product 16. 

[ppm1 · Accepted solution]

For that, you need a separate Date/Calendar table in your model with a 1:M relationship to the Date column in your table. You can then use a measure like this one to get your result. Replact T3Dates and T3 with your Calendar table and original table, respectively.

Latest =
VAR maxdate =
    MAX ( T3Dates[Date] )
RETURN
    CALCULATE (
        LASTNONBLANKVALUE ( T3[Date], SUM ( T3[Amount] ) ),
        REMOVEFILTERS ( T3Dates ),
        T3Dates[Date] <= maxdate
    )

Pat