Creating calculated column with max date from different table

[Veblengood]

I have a table with instore transactional data - table A, with data for each day, each product, each  sale and a table with detailed product conditions, e.g. cogs and profit for each product, which varyies by date - table B. The tables are indirectly connected with master data, one for date and for products with corresponding columns.

Table A

Product	Quantity	Month-year
A	1	01.04.2021
B	2	01.04.2022

Table B

Product	NSV	Month-year
A	10	01.05.2021
B	30	01.04.2022

I am trying to create a calculated column in table A, retreiving NSV-conditions from Table B with the powerbi lookup function. The issue is that not all months per year match with Table B, some will those retrieve blank values. Table B may lack some months, however, Table A will have all months included in the data set.  My idea is that if the lookup function retrieves blank values, then it will retrieve the max month-year value from table B under the condition that this max date is smaller than month-year from Table A in the given row.

I know this code is horrible, however, this is what I have tried:

NSV=

var mont_year=TableA[month-year]

return

if(isblank(LOOKUPVALUE(tableB[NSV] ,tableB[Product],TableA[Product], TableB[Month-year],'TableA[Month-year])),

LOOKUPVALUE(tableB[NSV] ,tableB[Product],TableA[Product], TableB[Month-year],calculate(max(TableB[Month-year]), allexcept(tableB[Product]), tableB[month-year]<manedar), LOOKUPVALUE(tableB[NSV] ,tableB[Product],TableA[Product], TableB[Month-year],'TableA[Month-year]))

The main issue with the formula is that calculate(max(TableB[Month-year]), allexcept(tableB[Product]), tableB[month-year]<manedar), does not retrieve the max date, but returns blank values. 

Do you have any thoughts?

[v-easonf-msft]

Hi, @Veblengood 

The solution provided by @Veblengood  seems to work.

If your problem has been solved, please accept the reply as solution to close this thread. Other community members will easily find the solution when they get the same issue.

Best Regards,
Community Support Team _ Eason

[johnt75]

You could use TOPN, e.g.

var monthYear = TableA[Month-year]
var prod = TableA[Product]
RETURN SELECTCOLUMNS(
    TOPN( 1,
        CALCULATETABLE( 
            TableB, 
            TableB[Month-year] <= monthYear,
            TREATAS( { prod }, TableB[Product] )
        ),
        TableB[Month-year]
    ),
    "NSV", TableB[NSV]
)

[amitchandak · Accepted solution]

@Veblengood , Try a new column like

new column =
var _max = maxx(filter(Table, TableB[Month-year] <= TableA[Month-year] && TableB[Product] = TableA[Product]),TableB[Month-year])
return
maxx(filter(Table, TableB[Month-year] =_max && TableB[Product] = TableA[Product] ),TableB[NSV])

[Veblengood]

@amitchandak

Thanks a lot, this did it for me! Just curious, do you use the maxx function in the last part of the syntax (maxx(filter(Table, TableB[Month-year] =_max && TableB[Product] = TableA[Product] ),TableB[NSV])) instead of lookup(), because maxx is faster and more efficient? Both should be able to retrieve the same result in this case.