Forum Discussion
Calendar-based Time Intelligence Preview - Issue
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
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
7 Replies
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!To understand this better, could you please share the Fiscal Calendar table that you have created?
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.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!
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
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.
