Distinct search/lookup

[Harry1980]

Hi all,

I have a little challenge where I struggle with and hope to find help here in this great community. What is the challenge?  For a certain material I need to search for the standard price valid in Sep 2021. In my table there might be a valid entry for this month and year, but not necessarily. If not available I would like to have the standard price with the closest earlier record to 09/2021. E.g. if there are records for 08/2021 and 07/2021 available then I would like to have only 08/2021 .  If there are only records available after 09/2021, then the expression should result in 0. 

As you can see in the table below for Material A it should select the record where year+period is 2030, for material B the record where year+period is 2029 ( there is no 2030 record and hence it should select the closest earlier record, namely August record). For material C July 2021 is the closest earlier record. And for material D no record should be selected as the records were created later than 09/2021

Is there any smart way in doing this?

Thank you in advance

Br

Harry

Material	Year	Pe	Year+Period	Standard price
A	2021	8	2029	8
A	2021	9	2030	9
A	2021	10	2031	10
B	2021	5	2026	2
B	2021	6	2027	3
B	2021	8	2029	4
B	2021	10	2031	5
C	2021	1	2022	1
C	2021	5	2026	2
C	2021	6	2027	3
C	2021	7	2028	4
D	2021	10	2031	1
D	2021	11	2032	2
D	2021	12	2033	3

[Harry1980]

thank you very much for your support. It is highly appreciated

[johnt75]

Try

Standard price = SELECTCOLUMNS(
	CALCULATETABLE(
		TOPN( 1,
			'Table',
			'Table'[Year], DESC, 'Table'[Pe], DESC 
		),
		ALLEXCEPT( 'Table', 'Table'[Material]),
		'Table'[Year] < 2021 || ( 'Table'[Year] = 2021 && 'Table'[Pe] <= 9 )
	),
	"@val", 'Table'[Year+Period]
)

[FreemanZ · Accepted solution]

hi @Harry1980 

try to plot a table with the material column and a measure like this:

Standard Price = 
VAR _targetmonth = 9
VAR _targetyear =2021   // the first two lines could be replaced other ways
VAR _material = MAX(TableName[Material])
VAR _table =     
FILTER(
        TableName, 
        TableName[Material]=_material
            &&TableName[Year] = _targetyear
            &&TableName[Pe] <= _targetmonth
    )

VAR _month = 
MAXX(
    _table,
    TableName[Pe]
)
RETURN 
MAXX(
    FILTER(_table, TableName[Pe]=_month),
    TableName[ Standard price ]
)

i tried and it worked like this: