I'm relatively new to Power BI, and struggling a bit to get data from one table filtered into another table, just not sure what way to do it using DAX , would be comfortable enough on how to do it in SQL but in this case I can't as the data from 1 of the tables must come in the form of an excel sheet that gets uploaded to PBI.
I have a REVENUE table which contains data on a transactional level for every Invoice produced, on a simple level it has columns for 'Invoice Number', 'Invoice Date', 'Item' and 'Sales Value'
Also I have a COST table, this comes from Excel, the item cost changes only every month (It's a complex calculation so maintained and updated in excel) , so this table has columns for 'Date', 'Item' and 'Cost'.
The REVENUE table and COSTS table are not linked together ( as they would be many to many), they link through a common ITEMS table where there is 1 record per item.
I want to bring the 'Cost' from the COSTS table into the REVENUE table and create a measure\column where the REVENUE table looks into the COSTS table and if the REVENUE 'Invoice date' matches the COST 'Date' and the REVENUE 'item' matches the COST 'Item' then pull in the COSTS 'cost' field
I suspect I have to use REALTEDTABLE function and some FILTER FUNCTIONS to do this but struggling to get the formulas correct, appreciate any guidance
