Latest Invoiced Value

[bhmiller89]

I have a table with invoice data. Each date has quantity invoices and I need to show only the most recent value.

One column is "Invoice Date" 

Another column is "Quantity"

Another calculated column I wrote is "Consolited Item"

Another Column is "Organization Name"

I need to write a measure that shows the sum of the most recent quantity invoiced. 

So, for Organization X if their last invoice was sent July 30th, what was the quantity of servers? 

[v-juanli-msft]

Hi @bhmiller89

How do you define "the most recent value"?

Best Regards

Maggie

[th3h0bb5]

That will depend some on your data. If the rows in your datasource are in order by date, you can use the LASTNONBLANK function in DAX to return the most recent record for each Date. Or you could set a filter in your table visualization that only returns MAX 1 invoice by Date.

[bhmiller89]

@th3h0bb5 I updated my request with more info if that helps

[th3h0bb5]

Here's my suggestion based on your update:

• Sort your data by date.
• Create a Max Date column using MAX(Table[Date])
• Create  Latest Quantity column using LASTNONBLANK(Table[Quantity],1)

Then you can create a table which will show you the max Date for any given Organization Name, and the latest Quantity that was invoiced on that date.

This may run into problems if you have multiple invoices a day. In that case, your Date column would need to include the time as well; then it will work fine.