how to get value from another table

[Anonymous]

Hi i have two table, one saletypsinfo like this:

date	salestype
2021-01-01	new year sales
2021-01-09	No sales
2021-02-15	feb sales
2021-02-20	No sales
2021-03-01	Season sales

and a facttables with date and sales like this:

date	sales
2021-01-01	100
2021-01-02	150
2021-01-03	400
2021-01-04	200
2021-01-05	400
2021-01-06	150
2021-01-07	150
2021-01-08	400
2021-01-09	400
2021-01-10	100
2021-01-11	400
2021-01-12	400
2021-02-15	200
2021-02-16	400
2021-02-17	150
2021-02-18	400
2021-02-19	200
2021-02-20	400
2021-02-21	400
2021-02-22	400
2021-02-23	100
2021-03-01	400
2021-03-02	400
2021-03-03	200
2021-03-04	400

I want to add the salestype column into the facttables to track what kind of sales is it, do anyone have idea?

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

Agree with the solution given by @amitchandak  and do the following test:

new column = 
var _max = maxx(filter(Saletypes, Saletypes[date] <='Fact'[date]),Saletypes[date])
return
maxx(filter(Saletypes, Saletypes[date] = _Max ),Saletypes[salestype])

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you it works fine with your solution.

[jmontes1810]

I think a LookUpValue in a new column on your facttables table is a easier way to do it.
Something like this....

New Column = LOOKUPVALUE(saletype[saletypsinfo],date[saletypsinfo],date[facttables])

*slaestypeinfo must cotain unique values in date column to work

[amitchandak]

@Anonymous , Try a new column like this in sales table sales

new column =
var _max = maxx(filter(saletypsinfo, saletypsinfo[Date] <=sales[Date]),saletypsinfo[Date])
return
maxx(filter(saletypsinfo, saletypsinfo[Date] = _Max ),saletypsinfo[salestype])