compute only the highest year and avoid duplication of data

[#1]

I have to compute the total tax pay per PIN. My PIN compose of (PCINUm,SINum,BInum,MDINum,Parcel,PType)

So far I have this expression.

sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PayBasic,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))
+ sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PaySEF,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))
- sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"}>} aggr(DISTINCT PayDiscount,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))

My problem is I have some duplication on my PIN and I have to identify their respective highest effectivity year. For Example;

PIN OwnerNum Effectivity Year Total Tax 
pin123  1003    2014    2,000.00 
pin123  1002    2009    3,000.00 
pin456  4002    2015    1,500.00 
pin456  4001    2014    900.00

I want to compute only for these data only;

I have two columns in my table and this is my desired output in SQL query;

PIN    OwnerNum  Effectivity Year Total Tax 
pin123  1003     2014             2,000.00 
pin456  4002     2015             1,500.00

I try these expressions but it gives me zero values;

sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr(DISTINCT max[yr] PayBasic,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) 
+ sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr(DISTINCT max[yr] PaySEF,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType)) 
- sum({<year1= {">=2015<=2015"},PayPenalty={"<=0"},max[yr]>} aggr (DISTINCT max[yr] PayDiscount,year1,OwnerNum,BINum,PCINum,SINum,MDINum,Parcel,PType))

[settu_periasamy]

Hi Mark,

Are you using straight table?

May be you can try like..

Dimension:

PIN

OwnerNum

Effectivity Year

Expression:

=sum({<[Effectivity Year]={"'$(=Max(Year))'}>}[Total Tax])

[effinty2112]

Hi Mark,

Try the attachment. It includes the following straight table:

PIN Owner	All Years' Tax	Most Recent Year	Most Recent Year's Tax
	7400	2015	3500
pin123	5000	2014	2000
pin456	2400	2015	1500