This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Creating complex custom date periods in calendar tables using calculated columns
This blog shows how to create custom date periods within a date table for 28 day cycles, but the principles shown could be applied to create any custom date periods.

Posted by Sam Lowrie on 15 May 2019

Date Calendar: Custom date codes

Creating custom date periods can be annoying, and takes a bit of practice. A delegate on one of my recent courses needed 28 day cycles, starting on the 1st of April and stopping on the 31st of March.

This particular code was comprised of P, the financial year plus 1 and then the current 28 day cycle within the financial year.

This blog works on the assumption that you have already created a date calendar (if you haven't, read this blog). The date table should look something like this:

Yours may not look identical (this will depend on your financial year end), but the 28 day cycle will work regardless.

Add a new custom column to the date table and start by capturing the row's value:

This first line stores the current row's date in a variable. For example for the first row in the above image it will hold the date of 01/04/2011.

Next we need to capture for any given year which date will be the very first in the 28 day cycles:

This example uses the first of April as the start date (change the 4 and 1 to whatever you need).

To start on a specific weekday such as the first Monday of a given year would require a bit of tweaking:

For the rest of the blog, the first of April will be used.

Using this we can assign which financial year the current month belongs to (that is, whether it is before or after the first date of the new financial year):

This compares the row's date to the stored first date of the financial year. Since January 2019 is before the first of April 2019, it will be given the year 2019 rather than 2020.

Now it is possible to assign each day to a 28 day cycle starting on the first of April:

Change the 28 to however many days you want in the date cycle.

This gives us the financial year and which 28 day cycle the date falls under. The final step is to combine them:

A lot of work, but you only need to do it once!

This should then give you something which looks like this:

The last day of the 2012 financial period is coded 1214 (financial year 2012, and in the 14th 28 day cycle). The first day of the 2013 cycle is 1301 (year 2013, and the 1st 28 day cycle).

If you want text in front of the code (or after it), add it into the CONCATENATE function at the end.

The full code for this example looks like this:

28 day cycle =

VAR CurrentRow = 'Date table'[Date key]

--Add filters to a given table

VAR FirstDateInYear = FILTER(

--Removes all previous filters

ALL('Date table'[Date key]),

--Only dates in current row's year

YEAR('Date table'[Date key]) = YEAR(CurrentRow)

--Month must be April

&& MONTH('Date table'[Date key] ) = 4

--Includes the first 7 days, as 1 of these will be a monday

&& DAY('Date table'[Date key]) = 1

)

VAR WhichFinancialYear =

IF(

--If the rows date is less than the start of the financial year

'Date table'[Date key] < FirstDateInYear,

-- Return current years code

YEAR('Date table'[Date key]),

-- Return current years code + 1

YEAR('Date table'[Date key]) + 1

)

VAR Cycle28Day =

CEILING(

IF(

CurrentRow < FirstDateInYear,

--Is the current row before the first date in the new year.

(DATEDIFF(DATEADD(FirstDateInYear,-1,YEAR),CurrentRow,DAY) +1)/28,

--Returns the days passed since the first day of the last financial year, divided by 28.

(DATEDIFF(FirstDateInYear,CurrentRow,DAY) + 1)/28

-- Returns the days passed since the first day of the current financial year, divided by 28.

)

,1)

--Rounds the values to the nearest whole number

VAR YearCycle =

CONCATENATE(

RIGHT( WhichFinancialYear, 2),

IF( LEN(Cycle28Day) =1, 0 & Cycle28Day, Cycle28Day)

)

Return YearCycle

An impressive formula!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Date Calendar: Custom date codes

Head office

London

Manchester