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

User_OY9UD

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

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!

User_OY9UD

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!