Hi Pigment Community ,

I’m working with a table that contains monthly revenue data across several years. My goal is to build a logic that, for each month, returns the latest available revenue value for that specific month, even if it comes from a previous year.

Objective

For each Month (e.g. Jan, Feb, … Dec), I want to retrieve the most recent actual revenue that is available for that calendar month, regardless of the year.

Example:

So in September 2025, for example, the latest available revenue for September would still be September 2024, if no more recent actual exists for September 2025 yet.

Why I'm doing this

I want to build a metric that compares Budget vs Actuals across future years, but always using the latest 12 months of actual revenue as the baseline.

That means I need a view where, for each calendar month, I can reference the most recent actual revenue available — and then use that to calculate year-over-year variations.

EXAMPLE

Below would be the matrix per year that combines the most recent actuals per month. The first semester of the year is 2025 actuals and the second semester of the year is 2024 actuals

Then I need to compare the actual baseline of the last 12 months with the output of the budget model:

🧩 What I’ve tried

'Revenue' aBY LASTNONBLANK: Month.'Month of Year'] OBY: Month.'Month of Year']

This result repeats the same values for each month of the year as expected but the numbers are wrong, they include values from previous years (2023 or 2022) that I don’t need.

Any suggestions?

Has anyone tackled something similar — selecting the latest value available per month name, across years?

Thanks in advance!

Laura