Lookup value per date

Question

Hi.&nbsp;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:&nbsp;Price table&nbsp;&nbsp;&nbsp;Article no.Date startDate end&nbsp;Price&nbsp;101.01.201830.11.2021&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,00101.12.202131.12.9999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00201.01.201831.12.9999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,00301.01.201831.01.2022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00301.02.202231.12.9999&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,00&nbsp;And I would like to have a visual like below:Visual:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nov-21dec-21jan-22feb-22% DevArticle 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 15,0025%Article 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 22,000%Article 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,004%&nbsp;I do have a date table as well and have tried a lot of different measures, but no one is working.&nbsp;Any suggestions?&nbsp;Thanks a lot in advance.Helen

erd · Accepted Answer

HEW&nbsp;, 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 &gt;= 'Table'[Date start],
        c_date &lt;= '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

Forum Discussion

Lookup value per date

2 Replies

Related content

Lookup between two dates

Lookup value if date is between two dates

lookup value within date range

LOOKUP VALUE between two dates

Lookup rank value?

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

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%