Max Date, by 1st Dimension, in Pivot

[Anonymous]

This should be simple but I can't seem to get it. I need a pivot to only show the most recent invoice date, by customer. This set analysis expression doesn't do it:  =sum({$<[InvoiceDate]={'$(=Max(Invoice_Date))'}>}[Sales Cases])          

[Anonymous]

Ok, I think got it. It may not be the best way but it seems to be working.

=if([Invoice Date]=max(total <[Ship to Customer Number]>[Invoice Date]),sum([Sales Cases]))

[Miguel_Angel_Baeyens]

Hi Brian,

Another option:

Sum(If(Aggr(NODISTINCT Max([Invoice Date]), [Ship to Customer Number]) = [Invoice Date], Cases))

It might worth testing how this expression performs.

Hope that helps.

Miguel

[Anonymous]

That seems to work in most cases but not all. Not sure why. The chart on the right is using your expression.

[Miguel_Angel_Baeyens]

Hi,

Probably because it needs another field [Ship To Customer Name] in the Aggr() function? Or may it be the NODISTINCT clause and account on purpose even when possible values for Max() are several in that aggregation?

Sum(If(Aggr(Max([Last Invoice Date]), [Ship to Customer Name], [Ship to Customer Name]) = [Last Invoice Date], Cases))

What the function does is very similar to yours, but using two aggregation functions which, for example, will return a total if there is a total line. The Aggr() part returns the Max() value of Last Invoice Date for each value in Ship to Customer Name, and if the value corresponds to the max, then Sum the values in Cases.

A FirstSortedValue() instead of Aggr() combined with the Sum() will likely work as well.

Hope that makes sense.

Miguel