How can i get price in selected date?

[Sergey_Bakaev]

Greetings!

I have a requirement for price tracking. I have tables with history of prices like this:

Also i have table with dates(calendar). So, i tried to calculate last price on selected date:

[[S]] changing 200-300] =
VAR Last_date =
CALCULATE(
    MAX('[S] products wholesale_prices_history'[changing_date]),
    FILTER(
        ALL('[TIMELINE] Date params'),
        '[TIMELINE] Date params'[Date]<=MAX('[TIMELINE] Date params'[Date])))

RETURN
SELECTCOLUMNS(
    FILTER(
        CALCULATETABLE('[S] products wholesale_prices_history', ALL('[TIMELINE] Date params')),
        '[S] products wholesale_prices_history'[changing_date]=Last_date
    ),
    "Price",
    MAX('[S] products wholesale_prices_history'[price])
)

But it gives me MAX of price before selected date(without MAX it gives me an error), but i want to take last price in selected date. For example:
case 1: I want to select date 20.06.23. It gives me following result.

Great, last price in selected date. But now i select 15.06.2023 or earlier, it gives me an error because more than 1 value got.
case 2: 

Table with price's history has rows with one date and different time changes. My formula сan't count prices if there were more than 1 change per day.
I have read this topic. But it didn't help me.
Can you help me with this task. I appreciate it!


upd:

i change a little bit  my formula:

VAR Last_date =
CALCULATE(
    MAX('[M] ozon prices_history'[changing_date]),
    FILTER(
        ALL('[TIMELINE] Date params'),
        '[TIMELINE] Date params'[Date]<=MAX('[TIMELINE] Date params'[Date]))
)

RETURN

IF(
    CALCULATE(HASONEVALUE('[M] ozon prices_history'[article]), ALL('[TIMELINE] Date params')),
    SELECTCOLUMNS(
            FILTER(
                CALCULATETABLE('[M] ozon prices_history', ALL('[TIMELINE] Date params')),
                '[M] ozon prices_history'[changing_date]=Last_date
            ),
        "Price",
        [price]
    ),
    "Choose product")​

But it still doesn't work if there were several changes on the same date.

To be clear, I want the last current price for each product to be shown when selecting the date. For example, if the last time the price changed was 19.06.23, then by selecting dates after 20.06.23, the price for 19.06.23 was shown.

[some_bih]

Hi @Sergey_Bakaev it seems from your formulas that you do not need column changing_time? If yes, remove column from your table (do not import it via Power Query or something like that) and see your results. Hope this help

[Sergey_Bakaev]

Hello @some_bih , thank you for your advice, but I think I will need this in order to filter the last time of the date that is selected.  Correct me if I'm wrong.

All the best

[some_bih]

Hi @Sergey_Bakaev in your picture wanted solution include article and date so time (hours and minutes) is not relevant. Hours and minutes are the lowest level of data so does not make sense to get some solution for that level of data as it can be easly filtered by date or article. So if you want to get the last time for some date then there is featues alreday - simple filter. This is the reason I "suggest" you to remove time column, if you want some solution using DAX. Hope this help

[Sergey_Bakaev]

I understood you. OK, I will stop using the column over time in the future.