5-4-4 Fiscal Calendar

Question

I am struggling with crating a fiscal calendar in Power BI. We follow a 5-4-4 week format, so for instance our 2015 fiscal year started Sunday, 12/28/2014.

While I downloaded a template, there are inherent flaws that when importing the dataset my numbers are becoming exaggerated.

Looking for some expertise on how I can implement a fiscal calendar that look forward from 2015 into the future.

Appreciate any help that the message board can provide.

anonymous · Answer

One big thing to keep in mind is that a custom calendar like that can potentially break a lot of time intelligence functions. You have to implement your own versions.&nbsp;So for example instead of&nbsp;&nbsp;Sales MTD = CALCULATE(
	SUM(SalesTable[Amount]),
	DATESMTD(DateTable[Date])
)&nbsp;&nbsp;...you may have to do something like&nbsp;&nbsp;Sales MTD = VAR enddate = LASTDATE(DateTable[Date])VAR mth = LASTNONBLANK(DateTable[Month of Year], 1)
RETURN CALCULATE(
	SUM(SalesTable[Amount]),
	FILTER(
		ALL(DateTable),
		DateTable[Month of Year] = mth &amp;&amp;
		DateTable[Date] &lt;= enddate
	)
)&nbsp;

anonymous · Answer

Indeed, that is what I am afraid of...&nbsp;I appreciate the feedback, but not sure I am closer how to do this.&nbsp;Are there resources or information that you think you can point me towards...

v-huizhn-msft · Answer

Hi&nbsp;Anonymous,You want to create a 5-4-4 Fiscal Clendar, there is a similar thread&nbsp;and a blog, please review and check if it helpful.Best Regards,Angelia

anonymous · Answer

v-huizhn-msft&nbsp;&nbsp;&nbsp;Thanks for the information. In what seems to be a more frustrating process, we do not have a "standard" 5-4-4 calendar.&nbsp;I have included a link to an Excel document which is a breakdown of 2015, 2016 and 2017 as we look at the fiscal year. &nbsp;Some of the nuances is an "extra week", which we add to Q3, and in the case of a fiscal month - it doesn't always coincide with a traditional calendar start/end. &nbsp;&nbsp;&nbsp;It will be expanded&nbsp;to include 2018 as well.&nbsp;Is there a way to join this with a dataset? &nbsp;When I attempt to join my dataset, I am getting this error:&nbsp;&nbsp;Thanks again!

v-huizhn-msft · Answer

Hi&nbsp;Anonymous,Based on the error message, you tried to create a relationship between two tables, one table is Fiscal Calendar table, what about another one?Best Regards,Angelia

Forum Discussion

5-4-4 Fiscal Calendar

7 Replies

Related content

Fiscal Calendar (4-4-5) Requirements DAX

4-4-5 Fiscal Calendar - Month number wrong!

4-4-5 Fiscal Calendar with Leap Year

Last Year measures with a NON traditional Fiscal calendar (4-4-5)

Subscriptions for 4-4-5 Fiscal Calendar