Variable in set analysis

[#1]

Hello Community,

.

I have a table that contains Ending balance for each period for each product

Then I created variable to display the most recent period for each project: vLastEndingBalance = Aggr(max(Period),[Project ID]).

Then I would like to create a pivot table that will show me the project ID and Ending Balance based on period selection, but when no period selected it should present the balance for most recent period.

So I created a pivot table with dimension "Project ID" and as expression I have:

=only({<Period ={"=$(=vLastEndingBalance)"}>} Ending_Balance).

It works fine when I select Period but when there is no selection the results of the expression gives me the earliest balance instead of recent

can you kindly let me know where I have a problem.

Thanks

[krishnacbe]

Try using below expression

=only({<Period ={"=$(vLastEndingBalance)"}>} Ending_Balance).

[sunny_talwar]

Not sure, but may be try this:

=Only({<[Project ID] = {"=Period = Max(TOTAL Period)"}>} Ending_Balance)

[sunny_talwar · Accepted solution]

Or this:

FirstSortedValue(Ending_Balance, -Period)

[#5]

That one worked!

Thank you Sunny.