Retreiving the Fiscal Year from Calendar Table with Indepedent Table

[HinW12]

Hi All

I’m working with a normal calendar table and have some FX conversion logic where I need to convert all historical transactions based on the current year. Because we use a custom fiscal year/month setup, I can’t rely on the standard time intelligence functions.

The problem I’m running into is that the Selected Year from the slicer gets “lost” once I use RemoveFilters in the logic.

I have a normal calendar table and have a bunch of logic for FX conversion where I need to convert all historical transactions based on the current year. I am not able to use the standard time intelligence function as we have a custom fiscal year and months.

What I’d like to know is: is there a way to retrieve the selected year from the Calendar table, but anchor it to another table instead? In other words, I want my anchor year to come from SelectedValue(NewTable[Year]). That way, the context transition remains intact since the anchor year is taken from the duplicate calendar table, not the original calendar table.

Just to add, it will need to be dynamic,  so calculated column will not work. If I select 2025 in the slicer from calendar table, that duplicate table also need to show 2025.

Has anyone dealt with this kind of setup, and is there a clean way to achieve this without reapplying all the logic?

Thanks in advance!

[v-priyankata]

Hi @HinW12 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@MFelix Thanks for the inputs

I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @HinW12 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[MFelix]

Hi @HinW12 ,

Currently you can create from the same calendar table different calendar views that will work properly with the time-intelligence functions.

Check the  links below:

https://powerbi.microsoft.com/en-us/blog/calendar-based-time-intelligence-time-intelligence-tailored...

https://learn.microsoft.com/en-gb/power-bi/transform-model/desktop-time-intelligence#calendar-based-...

This will allow you to have a single table that will acommodate different time periods.

[HinW12 · Accepted solution]

Thanks for this, I will read upon this but I have previously used preview features and sometimes it impacted lots of report such as the new cards which I had to adjust manually.