To lookup text from another table with the closest value (smaller/larger)

[cj_oat]

Hi everyone,

I would like to seek your advice as I'm trying to create a column to lookup text from another table based on closest value. Here is the table that I have;

This is Table1 where there is the data for product status

and table2 which I want to lookup product status in each month from Table1 but if the Month in Table2 is not available in Table1 then go to get the status from closest month e.g. 202401 go to get status from 202404 in Table1 while 202409 go to get status from 202408 in Table1 

This is what I'm using but it did not give me the result I expected

Product Status =
VAR TimeMonth = 'Table2'[Month]
VAR PastMonth = maxx(FILTER(ALL('Table1'[Month]),'Table1'[Month] <= TimeMonth),'Table1'[Month])

Return if(LOOKUPVALUE('Table1'[Product Status],'Table1'[Month],PastMonth,'Table1'[SKU],'Table2'[SKU])=BLANK(),"Active",LOOKUPVALUE('Table1'[Status],'Table1'[Month],PastMonth,'Table1'[SKU],'Table2'[SKU]))

This is the result that I get while I'm expecting the result in yellow column

Any advices or guidance would be really appreciated. Thank you!

[cj_oat]

Hi @Greg_Deckler and @NaveenGandhi ,

Thanks for the guidance and support! However, the result is still different from what I expected.

I have also input column for the result after putting DAX from both of your advices in Table 2 below,

My apology that in the first post I did not mention that each SKU will have different active/expired status in different month and each SKU will also have different lenght of month in Table 1 as well (e.g. SKU A0001 only have status from month 202404 - 202408 while SKU A0002 have status from month 202401-202411) while in Table 2 I want to maintain the same lenght of month from 202401 - 202412 for all SKU. So not sure if this is the reason that make it did not work...

Please let me know if anything I should further adjust in DAX

Table 1

A0001	202404	Active
A0001	202405	Active
A0001	202406	Active
A0001	202407	Expired
A0001	202408	Expired
A0002	202401	Active
A0002	202402	Active
A0002	202403	Active
A0002	202404	Active
A0002	202405	Active
A0002	202406	Active
A0002	202407	Active
A0002	202408	Active
A0002	202409	Active
A0002	202410	Expired
A0002	202411	Expired
A0003	202405	Active
A0003	202406	Active
A0003	202407	Active
A0003	202408	Active
A0003	202409	Expired

Table 2

Table 2				Expected Result
SKU	Month	Greg advice	Naveen advice	Product Status
A0001	202401		Active	Active
A0001	202402		Active	Active
A0001	202403		Active	Active
A0001	202404	Active	Active	Active
A0001	202405	Active	Active	Active
A0001	202406	Active	Active	Active
A0001	202407	Expired	Expired	Expired
A0001	202408	Expired	Expired	Expired
A0001	202409		Active	Expired
A0001	202410		Active	Expired
A0001	202411		Active	Expired
A0001	202412		Active	Expired
A0002	202401	Active	Active	Active
A0002	202402	Active	Active	Active
A0002	202403	Active	Active	Active
A0002	202404	Active	Active	Active
A0002	202405	Active	Active	Active
A0002	202406	Active	Active	Active
A0002	202407	Active	Active	Active
A0002	202408	Active	Active	Active
A0002	202409	Active	Active	Active
A0002	202410	Expired	Expired	Expired
A0002	202411	Expired	Expired	Expired
A0002	202412	Expired	Expired	Expired
A0003	202401		Active	Active
A0003	202402		Active	Active
A0003	202403		Active	Active
A0003	202404		Active	Active
A0003	202405	Active	Active	Active
A0003	202406	Active	Active	Active
A0003	202407	Active	Active	Active
A0003	202408	Active	Active	Active
A0003	202409	Expired	Expired	Expired
A0003	202410	Expired	Active	Expired
A0003	202411	Expired	Active	Expired
A0003	202412	Expired	Active	Expired

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in Table2

Closest month = LOOKUPVALUE(Table1[Product status],Table1[Month],coalesce(CALCULATE(MAX(Table1[Month]),FILTER(Table1,Table1[SKU]=EARLIER(Table2[SKU])&&Table1[Month]<=EARLIER(Table2[Month]))),CALCULATE(min(Table1[Month]),FILTER(Table1,Table1[SKU]=EARLIER(Table2[SKU])&&Table1[Month]>=EARLIER(Table2[Month])))),Table1[SKU],Table2[SKU])

Hope this helps.

[cj_oat]

Thanks @Ashish_Mathur ! This just works well for me and I get the result as expcted, appreciated your support!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@cj_oat Without sample data as text that I can copy and paste, I can't test this fully but something like this should work:

Column in T2 = 
  VAR __SKU = [SKU]
  VAR __Month = [Month]
  VAR __MinMonth = MIN( 'Table 1'[Month] )
  VAR __MaxMonth = MAX( 'Table 1'[Month] )
  VAR __Match = MAXX ( FILTER( 'Table 1', [SKU] = __SKU && [Month] = __Month ), [Product Status] )
  VAR __Result = 
    SWITCH( TRUE(),
      __Match <> BLANK(), __Match,
      __Month < __MinMonth, MAXX ( FILTER( 'Table 1', [SKU] = __SKU && [Month] = __MinMonth ), [Product Status] ),
      MAXX ( FILTER( 'Table 1', [SKU] = __SKU && [Month] = __MaxMonth ), [Product Status] )
    )
RETURN
  __Result

[NaveenGandhi]

HI @cj_oat 

You have almost done it right, The below DAX works.

Product Status =
VAR TimeMonth = 'Table 2'[Month]
VAR PastMonth =
MAXX (
FILTER ( ALL ( 'Table 1'[Month] ), 'Table 1'[Month] <= TimeMonth ),
'Table 1'[Month]
)
VAR AlteredMonths =
IF (
ISBLANK ( PastMonth ),
MINX (
FILTER ( ALL ( 'Table 1'[Month] ), 'Table 1'[Month] >= TimeMonth ),
'Table 1'[Month]
),
PastMonth
)
RETURN
IF (
LOOKUPVALUE (
'Table 1'[Product Status],
'Table 1'[Month], AlteredMonths,
'Table 1'[SKU], 'Table 2'[SKU]
)
= BLANK (),
"Active",
LOOKUPVALUE (
'Table 1'[Product Status],
'Table 1'[Month], AlteredMonths,
'Table 1'[SKU], 'Table 2'[SKU]
)
)

All i have done is add a new variable altered months, as the pastmonth variable only consideres months lesser than current month. 1st SS is for past month and 2nd is for alteredmonths.

Let me know if this helps.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[SamWiseOwl]

This will pull through the highest date on or before the current date.

Return Status =

var sku = [SKU]

var rowmonth = [Month]

var filttable = Filter('Table 1', [SKU] = sku && [Month] <= rowmonth)

RETURN

     MAXx( TOPN( 1,filttable,[Month],DESC),[Product Status])