Join two tables in Power BI with a Key and between dates

[yuvara]

Hi,

I am trying (struggling actually ...) to join two tables - Item Cost and Invoice, to get the Item Cost, per invoice.

Item Cost Table can hold different cost for different date periods for the same Item. And invoice table contains the Invoice ad Item info.

In SQL, I can join like : 

select
INV.Invoicenumber,
INV.ITEMID,
IC.Cost
FROM
InvoiceTable INV
outer left join Itemcost IC
on INV.Itemid = IC.Itemid
where
INV.Invoicedate >= IC.ItemCostStartDate
AND INV.Invoicedate < IC.ItemCostEndDate

is there any way, I can replicate this in Power BI way ? I.e. Merge queries or DAX ?

Thanks for your help.

[PattemManohar]

@yuvara It will be really helpful if you always provide sample test data and expected output, to suggest an accurate solution.

Anyway, here is the similar post that you are looking for.

https://community.powerbi.com/t5/Desktop/How-to-get-a-value-from-column-after-filtering/m-p/641805#M...

[yuvara · Accepted solution]

@PattemManohar : I think this will fix my issue but I'm not able to test it becase I'm getting the below error (the tables are really huge):

"There's not enough memory to complete this operation. Please try again later when there may be more memory available."

I will re-try and let you know my feedback.

Thanks again !

[yuvara]

Thanks ! It works !!