Pivot table: value of last month

Hi,

I have a pivot table with year and month in my rows fields and a kpi as a value.

I want to show the value for each month(no problem), and if I collapse it to the year I want to show the value of the last month (the problem).

year month kpi
2022 1 400
2 200
3 300
4 200
5 200
6 100
year month kpi
2022 100

I tried to use maxOver and lastValue, but then I have either the same value for every month or the aggregation of all months for the year.

Thomas

Hi Thomas,

Try this it may help you :slight_smile:

Regards,
Naveed Ali

Hi,

unfortunately this did not work. I either get no value for the year or it is aggregated.

Thomas

Hi Thomas,

I am not sure how this would be possible in the same column. The pivot table aggregation for kpi is at year and month level. When collapsed, based on the aggregation which has been set the value is displayed .

With periodOverPeriodLastValue - Amazon QuickSight , the total will not populate but the last month is visible when you collapse.

Regards,
Koushik