Find Last purchase cost

[Atinder]

Hello, 

I am need to Find my last invoiced purchased price and last Invoiced date. If there is no invoice leave it blank . I found the last invoice date with measure. 

Date of Invoice = LASTDATE( 'Query2'[Posting_Date]). 

I have 3 tables- 1 Sku's with decsription- vendor info, average unit cost

2: item ledger - all invoiced with date and price

3. Date table.

on table I pulled SKU  and unit cost from table1 and Date of invoice from table 2.  It pulls my cost per unit as Sum. When I select don't summerize  all Blank LAST purchase date goes away? How can I keep all blanks date of purchase and cost (dont' summerize)?

Thank you!

[v-jayw-msft · Accepted solution]

Hi @Atinder ,

Check the formulas:

last_date = CALCULATE(MAX('Purchase Rec'[Date Invoiced]),ALLEXCEPT('Purchase Rec','Purchase Rec'[Sku]))

last cost = CALCULATE(MAX('Purchase Rec'[COST]),FILTER(ALLEXCEPT('Purchase Rec','Purchase Rec'[Sku]),'Purchase Rec'[Date Invoiced]=[last_date]))

Best Regards,

Jay

[v-jayw-msft]

Hi @Atinder ,

Please share some sample data and expected result.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,

Jay

[Atinder]

hello@v-jayw-msft,

Here is link to my sample data.

Cost.pbix

Here is what I wanted to see.

Sku List	Dis	System Price	Last Invoice date	Cost
DIS-198	Item-1	$1.25	1/6/2022	$8

Thank you!

[Atinder]

Password for the file is 123456.