Lookup value per date

[HEW]

Hi.

I am trying to create a table, where I show the cost price for the first day of the month. We have a table with all cost prices, pls. see below:

Price table
Article no.	Date start	Date end	Price
1	01.01.2018	30.11.2021	12,00
1	01.12.2021	31.12.9999	15,00
2	01.01.2018	31.12.9999	22,00
3	01.01.2018	31.01.2022	25,00
3	01.02.2022	31.12.9999	26,00

And I would like to have a visual like below:

Visual:
	nov-21	dec-21	jan-22	feb-22	% Dev
Article 1	12,00	15,00	15,00	15,00	25%
Article 2	22,00	22,00	22,00	22,00	0%
Article 3	25,00	25,00	25,00	26,00	4%

I do have a date table as well and have tried a lot of different measures, but no one is working.

Any suggestions?

Thanks a lot in advance.

Helen

[ERD · Accepted solution]

@HEW , you can use the measure below:

PricePerStartOfMonth =
VAR c_article = SELECTEDVALUE ( 'Table'[Article no.] )
VAR c_date = SELECTEDVALUE ( Dates[StartOfMonth] )
RETURN
    CALCULATE (
        MAX ( 'Table'[ Price ] ),
        'Table'[Article no.] = c_article,
        c_date >= 'Table'[Date start],
        c_date <= 'Table'[Date end]
    )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

I am a Ukrainian living in Ukraine. Please, help us to survive! Please, Ask your government to react!
Here are official ways you can support us financially (accounts with multiple currencies):
https://bank.gov.ua/ua/about/support-the-armed-forces

USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708

Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces

[HEW]

It works perfectly!

Thanks a lot.

Helen