Max Payment Date

[#1]

Hi,

how to show  Transaction ID based  on Max Payment Date.

   Orginal Data:

Customer ID	Transaction No	Payment Date
csx01234d	snzcret34	7/15/2016
csx01234d	ynzcret35	7/16/2016
csx01234d	knzcre136	7/17/2016
csx23495	refrgmp532	7/18/2016
csx23495	tefrgmp533	7/19/2016
csx23495	hefrgmp534	7/20/2016
csx23458	kloiedftp13	7/21/2016
csx29458	ploiedftp14	7/22/2016
csx29458	zloiedftp15	7/23/2016
csx29460	lopredts24	7/24/2016
csx29460	fopredts25	7/25/2016
csx38570	sodbdu56	7/26/2016
csx38570	nodbdu57	7/27/2016
csx38570	modbdu58	7/28/2016

Output

Customer ID	Transaction No	Max Payment Date
csx01234d	knzcre136	7/17/2016
csx23495	hefrgmp534	7/20/2016
csx29458	zloiedftp15	7/23/2016
csx29460	fopredts25	7/25/2016
csx38570	modbdu58	7/28/2016

[tamilarasu]

Hi,

Data:
LOAD * INLINE [
Customer ID, Transaction No, Payment Date
csx01234d, snzcret34, 7/15/2016
csx01234d, ynzcret35, 7/16/2016
csx01234d, knzcre136, 7/17/2016
csx23495, refrgmp532, 7/18/2016
csx23495, tefrgmp533, 7/19/2016
csx23495, hefrgmp534, 7/20/2016
csx23458, kloiedftp13, 7/21/2016
csx29458, ploiedftp14, 7/22/2016
csx29458, zloiedftp15, 7/23/2016
  csx29460, lopredts24, 7/24/2016
csx29460, fopredts25, 7/25/2016
csx38570, sodbdu56, 7/26/2016
csx38570, nodbdu57, 7/27/2016
csx38570, modbdu58, 7/28/2016
];

Inner Join (Data)
LOAD  [Customer ID],
Max([Payment Date]) as [Payment Date]
Resident Data Group by [Customer ID];

Note: Updated my solution.

[tresesco]

Straight table:

Dimension : [Customer ID]

Expression 1 : FirstSortedValue([Transaction No], - [Payment Date])

Expression 2 : Date(Max([Payment Date]))

[Digvijay_Singh]

May be like this

[tresesco]

FirstSortedValue([Payment Date], - [Payment Date]) would be an overkill when the same can come directly from Max([Payment Date]), I guess.

[Digvijay_Singh]

Agree, Thanks for correcting!

[nikhilpotdar]

My Solution:

Straight table:

Dimension 1: [Customer ID]

Dimension 2 : [Transaction No]

Expression 1 : aggr(Max([Payment Date]),[Customer ID])

[soloeeeoff]

Please Try like this in chart

=max({1}[Payment Date])

=FirstSortedValue({1}TOTAL <[Customer ID]> [Transaction No],-[Payment Date])

In first sorted value, please note 'Minus Sign'

Thanks,

From Solomon Jeyaseelan