Sum of Max Date in Pivot Table(Fiscal-Year Drill Down)

[vaibhavb]

I have Pivot Table, Where used Fiscal Year (Financial Year) Drill Down as dimension and Sum of the Amount of the Period as Measure. Up to these its working fine, Now I want to add one more measure is Sum of the Max date of the Period but due to some technical difficulties i am unable to get it.

For more Information please attached Image :- 

1.  2017-2018, 2018-2019
2. ALL SUM :- Sum(DAYSCLOSINGBALANCE)
3. MAX Date :- max(PRODUCTDATE)
4. SUM of MAX Date : - SUM({$<BALANCEDATE=$(=MAX(BALANCEDATE))>} DAYSCLOSINGBALANCE)
5. Hard Coded Max date Sum : - Sum({$<PRODUCTDATE={'09/09/2018'}>} DAYSCLOSINGBALANCE)

Here "Hard Coded Max date Sum" column is just for the Test purpose, Which is exactly what i want into SUM of MAX Date(Dynamically instead of Hard coded value).  

 I tried with following condition :- 

  sum(DAYSCLOSINGBALANCE*if(BALANCEDATE=aggr(nodistinct max(BALANCEDATE), FiscalYear),1,0))

  But after drilling down(FiscalYear) it not working. e.g ->FiscalYear -> Fiscal Month -> BalanceDate

[Lisa_P]

Try this:
SUM({$<BALANCEDATE={'$(=MAX(BALANCEDATE))'}>} DAYSCLOSINGBALANCE)