Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

5-4-4 Fiscal Calendar

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 a...
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

 

So for example instead of

 

 

Sales MTD = CALCULATE(
	SUM(SalesTable[Amount]),
	DATESMTD(DateTable[Date])
)

 

 

...you may have to do something like

 

 

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 &&
		DateTable[Date] <= enddate
	)
)

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Indeed, that is what I am afraid of...

 

I appreciate the feedback, but not sure I am closer how to do this.

 

Are there resources or information that you think you can point me towards...

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

    You want to create a 5-4-4 Fiscal Clendar, there is a similar thread and a blog, please review and check if it helpful.

    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      v-huizhn-msft  

       

      Thanks for the information. In what seems to be a more frustrating process, we do not have a "standard" 5-4-4 calendar.

       

      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.  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.   

       

      It will be expanded to include 2018 as well.

       

      Is there a way to join this with a dataset?  When I attempt to join my dataset, I am getting this error:

       

       

      Thanks again!

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi 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