Help with Pivot Table

[#1]

I have a table that stores the end of day market values & the daily returns by date.

The table has multiple days & I need a pivot table that shows the latest day's market value & avg return by period (month, week etc).

See attached - I need the right formula to calculate the LatestMktValue calculated field in the "LatestMktValue" chart/table. (for now, I am using the sum(MktValue), which is obviously wrong)

We tried creating a separate table that stores the last day's assets for a week/month etc, but was hoping to find the right set expression that would give me the last day's assets in the given dimension.

I also expect to have additional filters (by month/week etc), so the formula needs to work with the given filters.

Appreciate any help.

Thanks

Milind

[#2]

Hi @M J

I have Expression for this you can try it and get back to me what is the result

In the Expression of Last Market Value you try this expression

Max(Mkt Value)

&

In Return Expression yo try this expression

Count(Return)

Try it

[#3]

this won't work as it would give me the Max(MktValue). I am looking for the MktValue as of the last day in the period (week, month, etc).