Fetch matching date value or latest date value

[tc_WII]

Hello, 

I have a data model with 2 fact tables: SalesData, ItemReceived, date and product table.

I want to create a simple table visual with Date, Average Selling Price (From Sales Data), Cost (From ItemReceived). There is a slicer to select product name.

Issue is with the Cost. Let's say, I have item received on 4/2/2024 and cost is $116. For any dates on or later than 4/2/2024, my cost is $116. Earlier item received was on 1/20/2024 and cost is $90. So, any dates between 1/20/2024 and 4/1/2024 has product cost of $90. This way I will calculate my profit like (ASP - COST)

I used below measure:

LastCostMod =
    CALCULATE(
        LASTNONBLANKVALUE(itemReceived[Date],MAX(itemReceived[Cost])),
        ALL(dimDate),
        itemReceived[Date] <= SELECTEDVALUE(dimDate[Date])
        )

It gives me good result on rows where I have dates. But it does not give me result on total and on Year-Month Tables.

Another solution could be to add cost on every row of my salesData. But it is not a good idea as I have sales data rows in millions.

How can I make a good measure so that it can also work on Year-Month table and totals?

My data model looks like this:

Please help. Thanks in advance

[parry2k · Accepted solution]

@tc_WII I think it should be:

LastCostMod =
    CALCULATE(
        LASTNONBLANKVALUE(dimDate[Date],MAX(itemReceived[Cost])),
       FILTER( 
        ALL(dimDate),
        dimDate[Date] <= MAX(dimDate[Date])
        )
)

[tc_WII]

This worked for now. Thank you 🙂