How to optimize dynamic fiscal year-end date calculation logic?

User_N2GB3

I’m using the below expression to derive the enterprise’s fiscal year-end date dynamically based on the current date:

TO_DATETIME(
CONCAT(
TRIM(TRAILING FROM CAST(
CASE
WHEN MONTH(CURRENT_DATE) IN (1, 2, 3) THEN YEAR(CURRENT_DATE) - 1
ELSE YEAR(CURRENT_DATE)
END AS CHAR
)),
'/03/31'
),
'YYYY/MM/DD'
)

This logic works correctly, but when I use it in other dependent calculations, the queries take around 3–5 minutes to load and display results

However, when I replace it with a constant date value like below, it performs much faster:

TO_DATE('2025/03/31', 'YYYY/MM/DD')

Is there a recommended way to optimize or handle such dynamic date calculations in FDI to avoid performance issues?

Bhaskar Konar

https://community.oracle.com/products/oracleanalytics/discussion/27376/how-to-optimize-dynamic-fiscal-year-end-date-calculation-logic

Hi @User_N2GB3,

Welcome to the Oracle Analytics Community!

Have you tried defining the calculated column in the semantic layer as a variable and then call it in report? This way it will be already calculated and might get some performance benefit.

Please take a look into the following documents / video.

https://docs.oracle.com/en/cloud/saas/analytics/25r3/fawag/add-session-variables.html

https://blogs.oracle.com/analytics/post/using-session-and-repository-variables-in-oracle-analytics

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/semantic-model-variables.html

https://www.youtube.com/watch?v=VXIrHI02HKg

Hope this help.

Thank you.