Version 16

# Description

This is a collection of some common periodizations (range of dates).

I could not for the life of me find a collection of calculations similar to this, so I went and created this myself.

Luckily, the calculations follow a pattern that is easy to recognize, so customizing one for, say, previous 3 months, should be trivial once you get used to using the calcs.

I have added colors for easier recognizing the pattern.

Currently on the to-do list:

- expand list to include relative periods

- generator for custom periods

- a generic model for universal use for any period

- expand periodization to include time

- more consideration for iso-datetimes

# How to use the premade calculations!

1. Create a calculated field and copy/paste your desired period from the list below.
2. Replace [Selected_Datetime] with either a fixed date, a parameter date or today()
3. Replace [datetime_data] with the date field in your data. It will typically be a dimension.
! The list below is optimized for dates only, not datetimes, so remember to format if needed.
4. You can now use the calc as a boolean statement.

I have attached a workbook including all calculations below. The workbook is very simple but can be used to check if the periodization is as expected.

# Period-to-Date

• YTD

DATETRUNC( 'year' , [Selected_Datetime] ) = DATETRUNC( 'year', DATE( [Data_Datetime] ) ) AND DATE( [Selected_Datetime] ) >= DATE( [Data_Datetime] )

• YTD, previous Year

DATETRUNC( 'year' , DATEADD( 'year', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'year', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'year', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• YTD, previous Quarter

DATETRUNC( 'year' , DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'year', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• YTD, previous Month

DATETRUNC( 'year' , DATEADD( 'month', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'year', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'month', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• YTD, previous Week

DATETRUNC( 'year' , DATEADD( 'week', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'year', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'week', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• QTD

DATETRUNC( 'quarter' , [Selected_Datetime] ) = DATETRUNC( 'quarter', DATE( [Data_Datetime] ) ) AND DATE( [Selected_Datetime] ) >= DATE( [Data_Datetime] )

• QTD, previous Year

DATETRUNC( 'quarter' , DATEADD( 'year', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'quarter', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'year', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• QTD, previous Quarter

DATETRUNC( 'quarter' , DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'quarter', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• QTD, previous Month

DATETRUNC( 'quarter' , DATEADD( 'month', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'quarter', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'month', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• QTD, previous Week

DATETRUNC( 'quarter' , DATEADD( 'week', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'quarter', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'week', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• MTD

DATETRUNC( 'month' , [Selected_Datetime] ) = DATETRUNC( 'month', DATE( [Data_Datetime] ) ) AND DATE( [Selected_Datetime] ) >= DATE( [Data_Datetime] )

• MTD, previous Year

DATETRUNC( 'month' , DATEADD( 'year', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'month', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'year', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• MTD, previous Quarter

DATETRUNC( 'month' , DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'month', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'quarter', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• MTD, previous Month

DATETRUNC( 'month' , DATEADD( 'month', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'month', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'month', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

• MTD, previous Week

DATETRUNC( 'month' , DATEADD( 'week', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'month', DATE( [Data_Datetime] ) ) AND DATE( DATEADD( 'week', -1 , [Selected_Datetime] ) ) >= DATE( [Data_Datetime] )

Remember to add "iso-" before "week" and "weekday" if applicable!

• WTDay (i have substituted WTDate with WTDay because week numbers are not associated to fixed dates). (Also, I have omitted Quarter and Month, as week no. does not relate to these time formats)

DATETRUNC( 'week' , [Selected_Datetime] ) = DATETRUNC( 'week', DATE( [Data_Datetime] ) ) AND DATEPART('weekday', [Selected_Datetime] ) >= DATEPART('weekday', [Data_Datetime] )

• WTDay, previous Year

DATETRUNC( 'week' , DATEADD( 'year', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'week', DATE( [Data_Datetime] ) ) AND DATEPART('weekday', [Selected_Datetime] ) >= DATEPART('weekday', [Data_Datetime] )

• WTDay, previous Week

DATETRUNC( 'week' , DATEADD( 'week', -1 , [Selected_Datetime] ) ) = DATETRUNC( 'week', DATE( [Data_Datetime] ) ) AND DATEPART('weekday', [Selected_Datetime] ) >= DATEPART('weekday', [Data_Datetime] )

Tableau Version: 2019.1.3

Original Author: Christian Binderkrantz