Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have two tables, a FactSales table and an ItemPrice table. The FactSales table has an item number and an invoice date. The ItemPrice table has an item number, an effective date, an expiration date, and a price (so the item number is not unique in this table - an item has multiple entries for prices at different times). I want to pull the price of the item at the time of the invoice into my FactSales table.
I tried to embed a lookupvalue in a calculate with filters on the ItemPrice table based on eff. and exp. date, but I realized I couldn't actually use lookupvalue inside calculate. I'm stuck on this one.
Table examples:
ItemNumber | Eff. Date | Exp. Date | Price |
1A | 1/1/2020 | 1/1/2021 | $10 |
1A | 1/1/2021 | $12 |
ItemNumber | InvoiceDate | Price (What I want) |
1A | 6/3/2020 | $10 |
1A | 4/1/2021 | $12 |
Solved! Go to Solution.
Hi @Brotedo ,
You can create a calculated column in the table 'FactSales' as below to get it, please find the details in the attachment.
Price =
CALCULATE (
MAX ( 'ItemPrice'[Price] ),
FILTER (
'ItemPrice',
'ItemPrice'[ItemNumber] = 'FactSales'[ItemNumber]
&& 'FactSales'[InvoiceDate] >= 'ItemPrice'[Eff. Date]
&& 'FactSales'[InvoiceDate]
<= IF (
ISBLANK ( 'ItemPrice'[Exp. Date] ),
DATE ( 9999, 12, 31 ),
'ItemPrice'[Exp. Date]
)
)
)
Best Regards
Hi,
In the Query Editor, insert Today's data in place of blanks in the Exp. Date column. In Table2, write this calculated column formula
Price = calculate(SUM('Table1'[Price]),filter('Table1','Table1'[Itemnumber]=earlier('Table2'[Itemnumber])&&'Table1'[Eff. Date]<=earlier('Table2'[Date])&&'Table1'[Exp. Date]>earlier('Table2'[Date])))
Hi @Brotedo ,
You can create a calculated column in the table 'FactSales' as below to get it, please find the details in the attachment.
Price =
CALCULATE (
MAX ( 'ItemPrice'[Price] ),
FILTER (
'ItemPrice',
'ItemPrice'[ItemNumber] = 'FactSales'[ItemNumber]
&& 'FactSales'[InvoiceDate] >= 'ItemPrice'[Eff. Date]
&& 'FactSales'[InvoiceDate]
<= IF (
ISBLANK ( 'ItemPrice'[Exp. Date] ),
DATE ( 9999, 12, 31 ),
'ItemPrice'[Exp. Date]
)
)
)
Best Regards