How to pick the most recent month ?

[#1]

PFA Sample QVW.How to pick the most recent month for each ID?

Thanks much.

[pk2019]

Hi Pavana,

The answer is found in this post:

Get Max Date and Max Value of a ID

Cheers,

Paul

[Gysbert_Wassenaar]

Try:

=FirstSortedValue( aggr(sum(Amount),[Invoice Month],Code), aggr(-max(match([Invoice Month], 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') ),[Invoice Month],Code))*12

Alternatively replace your textual month field with a field that has a numeric value. That way you can replace the max(match(... )) construction with the name of the numeric field.

[sunny_talwar · Accepted solution]

A slightly different way to assign numerical value to month:

=FirstSortedValue(Aggr(Sum(Amount), [Invoice Month], Code), -Aggr(Max(Date#([Invoice Month], 'MMM')), [Invoice Month], Code)) * 12