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-yueyunzh-msft · Accepted solution]

Hi , @Atinder 

You can use these dax measures :

Last Invoice date = LASTDATE('Purchase Rec'[Date Invoiced])

Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

(2)Then you can put them on the visual , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft]

Hi , @Atinder 

According to your description, you want to calculate the sum of [Cost_per_Unit] in the last date. Right?

You can try this dax :

CALCULATE(SUM('Query2'[Cost_per_Unit]),LASTDATE( 'Query2'[Posting_Date]))

For your question, I am not very clear about your relationships and needs between tables, you can provide your .pbix file and provide the output examples you want in tabular form so that we can better help you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Atinder]

Hello,

Here is my sample data Cost.pbix.

And this is what i am trying to accomplish in a table. 

Sku List     Dis        System Price    Last Invoice date     Cost   

DIS-198    Item-1       $1.25                  1/6/2022          $8

Thank you

[v-yueyunzh-msft]

Hi , @Atinder 

The .pbix file share link need a password, i cannot download it , can you share it again so that we can help you better?

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Atinder]

Hi @Aniya,

Password for the file is 123456.

Thank you

[v-yueyunzh-msft · Accepted solution]

Hi , @Atinder 

You can use these dax measures :

Last Invoice date = LASTDATE('Purchase Rec'[Date Invoiced])

Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

(2)Then you can put them on the visual , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Atinder]

Hi@Aniya,

Thank you it works. 

but I have another question If I have 3 invoices from same date. I want to look up value from the last invoice date and time. How can I lookup that up?

For example: I want to see like this DIS-198  Last invoiced: 1/6/2022   cost:$8.

when I use this formula it gives me Sums up all the cost from 1/6. I want to the most recent one. 

Cost = CALCULATE( SUM( 'Purchase Rec'[COST]) , LASTDATE('Purchase Rec'[Date Invoiced]))

I attached file

Cost (1).pbix

Pass: 123456

Thank you!

[v-yueyunzh-msft · Accepted solution]

Hi , @Atinder 

You can update the measure to this:

Last Invoice date = MAX('Purchase Rec'[Date Invoiced])

Cost = 
var _last_date  = MAX('Purchase Rec'[Date Invoiced])
return
CALCULATE( SUM( 'Purchase Rec'[COST]) , 'Purchase Rec'[Date Invoiced]=_last_date)

Then we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly