Calculating "period to date" - a collection

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

    - add fiscal periods

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

     

    Common Periodizations

    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