LASTDATE with conditon

DAX DAX Calculations
1

Hello all,

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

EDIT

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)

2

I believe this is what you want
If you want to sum all the invoices in the last day, than use the SUM() otherwise you may use lastnonblank() that if will get you last result

also you can improve with another top1 for order by invoice num for example…

Last Invoice Annual Amount = 
CALCULATE( 
    LASTNONBLANK(Invoices[InvoiceAmount];1); // if you only wants to get the last value 
     //SUM(Invoices[InvoiceAmount]); // if you want to sum() the amount in the last day
     TOPN(1;FILTER(Invoices;Invoices[Invoice Description] = "Annual");Invoices[InvoiceDate];DESC)
)

that’s not the best perfomance way to do it, but if you not working with 5mm+ lines that its ok…
Regards

1 Like
3

Thats the ticket. Thanks @andrejaar!