Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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])
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
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]
)
@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])
@amitchandak
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |