Oracle Communities

Oracle Analytics and AI

Child Item

How to get the Full Year based on Current Month in OAC DV?

Hello All,

We have a requirement in OAC DV to display the full year's amounts/values of data dynamically based on the currently selected period filter. Specifically, the objective is to show accumulated values for the year up to and including the current month.

The data is derived from a financial subject area, and we are open to implement this, using filters, parameters, or custom expressions. Are there best practices or examples available that can guide us to set this up effectively?

Thank you

Find more posts tagged with

Reporting

Parameter

Filters

Customizing Reports

OAC

Accepted answers

Hesham Khalil ©

In case anyone's looking for an answer, I’ve successfully made it work:

FILTER(AGGREGATE(Column AT Fiscal Year) USING Fiscal Year = '2025')

You can also replace '2025' with a Year parameter, so the calculation will be dynamic.

All comments

Sumanth V -Oracle

@User_OY9UD - Welcome to Oracle Analytics Community!

Use a dashboard-level period filter (e.g., Month or Date) to capture the current selection.

Create a calculated measure using a filter expression that includes all months from the start of the year up to the selected month.

Example:
FILTER("Amount" USING "Period" <= VALUEOF("Selected_Period") AND "Period" >= FIRSTOFYEAR("Selected_Period"))

This setup allows the values to adjust dynamically based on the selected month, always reflecting the year-to-date totals.

Thanks for using the community!

Hesham Khalil ©

Hi @Sumanth V -Oracle,

Thanks for looking into my query.

I appreciate the reasoning behind your measure and would like to gain clarity on its implementation, as I believe it has the potential to benefit the community.

One more thing to clarify, FIRSTOFYEAR is not available in DV, right?

Also, Period is a data type of text.

Here are the steps I took so far:

1- Created a parameter to return the list of selected periods:

2- Create a calculated measure: The screenshot below demonstrates two approaches for the measure, and I am uncertain about which one is preferable:

Option A (highlighted in red): The filter expression (your code) substitutes relevant fields/columns, but I’m unsure how to replace the 'Selected Period"?
Option B (highlighted in blue): The same filter expression, replaces the 'Selected Period' with the @Parameter ("p_Selected_Period") (default value). I don't think I can leave the default value blank/empty so it will always get the parameter value?

Thank you!

In case anyone's looking for an answer, I’ve successfully made it work:

FILTER(AGGREGATE(Column AT Fiscal Year) USING Fiscal Year = '2025')

You can also replace '2025' with a Year parameter, so the calculation will be dynamic.

Bhaskar Konar

Very helpful & useful information.

Thanks for sharing guys!