Calendar-based Time Intelligence Preview - Issue

[sebastienm1]

Hi,

Using the Preview of the Calendar-Based Time Intelligence , I have created a Fiscal Calendar in order to simplify the measures' expressions. However, one of the Associated Columns is giving me an error and I cannot figure out why. See picture:

On the left, clicking "Validate Data" shows an issue: it says that it was expecting a one-to-one relationsship between Fiscal Year and Relative Fiscal Year. However, as you can see in the table, it is a one-to-one.
Both fields are defined as Whole Number.
Any idea? Thank you.
Sebastien 

[sebastienm1 · Accepted solution]

Hi all,

Over the weekend, I found out that the validation of the calendar was failing with Calculated Columns, so I moved the computation to Power Query instead and this fixed the issue. As far as I can tell, this isn't mentioned in the documentation. It took me a while to figure this one out. 
Thank you for your help,

-- Sebastien

[v-sshirivolu]

Hi @sebastienm1 ,
Thanks for sharing your insgihts, this is really helpful. What you observed does make sense, as the calendar-based time intelligence preview seems to work more reliably when the required date columns are created during data load rather than as DAX calculated columns. Moving the logic to Power Query makes those columns part of the model itself, which allows the validation to pass. So your workaround will definitely help others who run into the same issue.

[sebastienm1]

Hi. Thanks for the reply @grazitti_sapna .

Having multiple rows with the same values shouldn't be an issue, as long as each Fiscal Year value has a single Relative Fiscal Year value and, vise versa, each Relative Fiscal Year value has a single Fiscal Year value.  
A similar pair that works just fine -- and you can see in my previous post -- is the Fiscal Quarter and Fiscal Quarter Numeric.
@InsightsByV , my Calendar table -- with Relative Fiscal Year selected, showing its expression -- from which i created a custom calendar 'Fiscal' as shown in my first post:

I noticed that the expression for Relative Fiscal Year uses a measure based on this same Calendar table, and while it works fine in a regular calendar, it is maybe creating a conflit in a Custom Calendar.
I replaced the expression by the temporary one : 2007 -'Calendar'[Fiscal Year], and again tried adding the column as Associated Column. Unfortunately, same issue when i click Validate data.

[InsightsByV]

Hi,

Can you try creating separate table for years?  Year =
SUMMARIZE(
'Calendar',
'Calendar'[Fiscal Year],
"Relative Fiscal Year", MAX('Calendar'[Relative Fiscal Year])
)"

then try to use fields from this above table instead of the main one?

Let me know if that helped!

[sebastienm1]

Thank you @InsightsByV ,

With the "Calendar-based Time Intelligence" feature (doc here), you cannot use mutiple tables,i.e you create a fiscal calendar on top of a 'calendar' table which must contain all the fields you'll need for the fiscal calendar.

[grazitti_sapna]

Hi @sebastienm1,

Issue seems to be Multiple rows per fiscal year in your base table

For e.g.

You can fix it by creating a mapping table like below

FiscalYearMapping =
DISTINCT (
SELECTCOLUMNS(
'Date',
"Fiscal Year", 'Date'[Fiscal Year],
"Relative Fiscal Year", 'Date'[Relative Fiscal Year]
)
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[InsightsByV]

To understand this better, could you please share the Fiscal Calendar table that you have created?