I have a scenario where I need to get the last invoice amount for a contract, but have some gotchas on it, specifically around the Invoice Description.
What I have now are some base measures:
* TotalInvoice = SUM(Invoice[Amount]) * Last Invoice Amount = CALCULATE( [Total Invoiced], LASTDATE( Invoices[InvoiceDate] ), FILTER( ALL( Dates ), Dates[Date] <= TODAY()))/*this is to account for invoices with a future date that will not count*/
What I’ve done is add in a search to the criteria, which does not seem to be working correctly:
*Last Invoice Annual Amount = CALCULATE( [TotalInvoiced], FILTER( Invoices, SEARCH( "Annual", Invoices[Description], 1, -1 ) > 0 ), LASTDATE( Invoices[InvoiceDate] ) )
This gives me blanks, but if I switch the search to not contain “Annual”, I get the correct result for that context.
Essentially, we have invoices that are sent out for say a yearly renewal. There may be an additional invoice sent a month later for a much smaller amount for “Additional charges” around true-ups. When we show the last invoice amount, we want to only pull in the annual charges, which say annual in their description.
I’m creating a dummy model now to really show the issue.
Any thoughts would be greatly appreciated
After shrinking, the search is working correctly, its showing blank if the description does not contain “Annual”. What I need it to do is show the value from the last invoice with the annual notationlastdate.pbix (896.0 KB) lastdate.xlsx (10.8 KB)