Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Accepted answer
60
Views
4
Comments
Hesham Khalil ©
Hesham Khalil © Rank 3 - Community Apprentice
in Oracle Fusion Data Intelligence

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?

image.png

Thank you

Tagged:

Best Answer

  • Hesham Khalil ©
    Hesham Khalil © Rank 3 - Community Apprentice
    Answer ✓

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

    image.png

    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.

Answers

  • Sumanth V -Oracle
    Sumanth V -Oracle Rank 8 - Analytics Strategist

    @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 ©
    Hesham Khalil © Rank 3 - Community Apprentice
    edited Apr 26, 2025 12:12PM

    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:

    image.png

    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?
    image.png

    Thank you!

  • Bhaskar Konar
    Bhaskar Konar Rank 8 - Analytics Strategist

    Very helpful & useful information.

    Thanks for sharing guys!