Use built in time intelligence with Custom Calendar? NEXTQTR etc

[pete234]

I may have once asked about this in the main forum for regular Excel (no replies as I recall) but it seems I do a lot of work that is tied to a flat fiscal table (4-4-5) for filters/labels but I don't currently do a lot of comparison between Quarters or Periods or Years (first one about to end).
This may change fairly soon as the year is closing out so I wondered if there is anyway to use NEXTQTR or especially
SAMEPERIODLASTYEAR (Date_Column) with a custom fiscal table?

Do any of these functions work against anything but a normal calendar?
It seems now--although I've just started looking at some of these--it would be a lot work without these to help.

Any ideas appreciated I am hopefully missing something. thanks

 

[Laurent C]

Time intelligence functions are not designed to be used with fiscal calendars (of the 4-4-5 or 5-4-4 calendars kind).

The good thing with these calendars is, that every quarter (and year) always have the same number of months, weeks and days, and that every week starts and ends within the same month.

You can use DATEBETWEEN with some intermediary calculations to do this.

Assuming a FiscalCalendar table with the following columns:
Date
Day In Week
Quarter In Year
Year
Week In Month
Month In Quarter
Month In Year
Quarter Month

The following FILTER expression offsets all quarters within the current context by 1:

         FILTER(
  ALL(FiscalCalendar),
  CONTAINS(
   SUMMARIZE(
    FiscalCalendar,
    [Year], [Quarter In Year],
     "Quarter Key" , [Year]* 4 + [Quarter In Year]
    
   ), [Quarter Key], [Year]* 4 + [Quarter In Year] -1
  )
         )

Note the expression [Year]* 4 + [Quarter In Year] uses the fact there are 4 months in a year. You could make calculations on months or weeks by using 12 or 52 instead of 4.

Note that you may want to keep the selection of certain attributes ([Day In Week], [Month In Quarter]) intact.

Altogether, this would look like:

=CALCULATE( [Whatever],
 FILTER(
  ALL(FiscalCalendar),
  CONTAINS(
   SUMMARIZE(
    FiscalCalendar,
    [Year], [Quarter In Year],
     "Quarter Key" , [Year]* 4 + [Quarter In Year]
    
   ), [Quarter Key], [Year]* 4 + [Quarter In Year] -1
  )
         ), 
 
         VALUES(FiscalCalendar[Day In Week]),
         VALUES(FiscalCalendar[Month In Quarter])
             
 )

 

[pete234]

Laurent thanks as always for your help.
However, the premise you state of always starting on the same week and number of weeks doesn't seem to hold up on my calendar.
I have weeks that end the month in the next calendar month and some years are 53 weeks, not 52.
Periods also end a before the month is up or into the next calendar month.
Our weeks start Sat and End Friday, that is definitely the one consistent.

 

[pete234]

Thanks as always for your help.
But my table doesn't always have weeks ending in the same month it started.
Last weeks of each month can be in same month or next calendar month.
And some years are 53 weeks not 52. Does that impact this scenario?
Weeks are Saturday to Friday.
I will definitely check out the formula though.

 

[Jacob Barnett]

Pete, as Laurent says the standard time intelligence stuff isn't much use! Here a couple of tips I can pass on from my long tortuous odyssey with 4-4-5:

1. I've had great success with extra columns in my calendar table source data which are purely there to help with time intelligence. These include:

• Week Status i.e. This Week, Last Week, Last Week-1, Last Week Last Year etc. This means I can write a really simple CALCULATE measures and gain a useful 'dimension' which I can use as a report filter or even as part of a set.

• Quarter Serial i.e. Every quarter in my calendar is assigned a unique ascending number. Using this I can write a far simpler 'vs Prior Quarter' measure using CALCULATE, FILTER and ALL which in my experience outperforms some of the cleverer solutions and can be written and understood by mortals.

2. There is one time intelligence function I use repeatedly which is 'DATEADD'. There is an inherent advantage in the 4-4-5 calendar in that its uniform and symmetrical (which is why its used) which means that something like:

[Sales PY]=CALCULATE([Sales],(DATEADD('Fiscal Calendar'[Date],-364,DAY))) will always give you the right YOY comparison and can be adapted for numerous time frames by changing the number of days (e.g. 91 for Quarters).

3. The 53rd Week is a Pain but this isn't unique to PowerPivot!!! I work in retail where ensuring the right prior comparison is crucial and outweighs and pedantry when it comes to nomenclature - this means that when the prior year had 53 weeks (which is the case now) I basically re-write history and change the prior year week numbers so the comparisons line up (this may be controversial in larger businesses). The DATEADD / -364 technique above is also brilliant in this respect as it doesn't care whether your years have 52 or 53 weeks.

Hope this helps.
Jacob

 

[Laurent C]

Hi Pete,

well if my premise does not hold, it may not be a true 4-4-5 calendar

When saying "every week starts and ends within the same month", I meant fiscal month. If this is not the case, you might want to provide details about this calendar. (Summarized of course)

I guess, "they" sometimes squeeze an extra week is in the last quarter. The formula I provided should work for quarters, and months, though.

In that case, the formula will of course not work for weeks. Fortunately, relying on weeks always having 7 days, the formula below should work:
"Week Key" ;QUOTIENT([Date] - DATE(2011;01;01);7)

And if, for some reason, the assumption does not hold, you still have the possibility to introduce an additional column in your data with a sequential key for the week.

 

[miguel.escobar]

You guys should read Rob's book. It provides a whole chapter on customized calendar tables. I had to create my own calendar table where I hold Fiscal, retail and even some random week designation that the use in the company that I work with. In the end, it all comes down to how much you know about your business and how you want to analyze it.

Best!
Miguel

 

[pete234]

Miguel that is a good point, I just picked up powerpivot pro book and found this chapter recently but
wanted to get through Measures first because I have a feeling I've been missing something there.
Thanks everyone else for your responses I will hopefully be able to experiment this week.

 

[miguel.escobar]

Experiment! best way to get to know this is by experimenting and then seeing the results by yourself.
I'm not going to lie, there are a few calendar table templates around the web that you could use if your business has a really standardized way of analyzing but it's always better if you create your own.

I'm here to help if you have any other questions and Laurent & Jacob are pretty regular here so, you have a good support team going on in here!

 

[powerpivotpro]

Hi guys thanks for the endorsement. Glad you guys are enjoying the book