Forum Discussion

Harry1980's avatar
Harry1980
Helper I
3 years ago
Solved

Distinct search/lookup

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

MaterialYearPeYear+Period Standard price 
A 202182029                                        8
A 202192030                                        9
A 2021102031                                      10
B202152026                                        2
B202162027                                        3
B202182029                                        4
B2021102031                                        5
C202112022                                        1
C202152026                                        2
C202162027                                        3
C202172028                                        4
D2021102031                                        1
D2021112032                                        2
D2021122033                                        3
     
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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:

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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]
)
  • Harry1980's avatar
    Harry1980
    Helper I
    3 years ago

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