Hi,

There is a concept of datasets hierarchies, that I imagine will allow you to design what you ask. But it is a future feature listed in the 2024 Q3 roadmap (on the right of the page)

I must admit I never used it on dataset, because it's a "wrong implementation breaking the concept of data modelling" for me (time series should work on hierarchies, because that's how you can be sure of what query is being executed and what the meaning of that calculation is).

My understanding is that you do not create it as to date for fiscal year, but just create the calculation as todate(Monthly Rate (CAD), YEAR) . This give you a dynamic calculation that will work at the year level depending on what date column you have in your visualization. I didn't try to see what happen if you have 2 dates, it will either give you an error or work just on the first one.

The YEAR reference isn't a column, it will reference the "virtual" Year representation of the date column.

I just tested in OAS 2024, and it does indeed work like that: a single calculation giving me todate(year) for ship date and for order date, just because I have 2 different visualizations with the 2 dates.