Bi-weekly data grouping

Version 2

    Description:

    This calculation will take any date field and aggregate it at a bi-weekly level. This is useful for reporting on data by period if your company does bi-weekly pay periods. By default, this will work on weeks that start on Sundays. You can change that by setting the week start day to a different day of the week (right click on data source > date properties).  To make this calculation work, replace [Date Field] with your own date field and set the reference date to the beginning of a bi-weekly period. For my company, #7/27/2014# represented the beginning of a pay period. The one you pick is arbitrary.

     

    Blog post: Tableau Zen: Calculation to group data into bi-weekly periods

     

    Example Calculation:

     

    IF DATEDIFF('week',DATETRUNC('week',[Date Field]),#7/27/2014#)%2=0 THEN

        DATEADD('week',2,DATETRUNC('week',[Date Field]))-1

    ELSE

        DATEADD('week',1,DATETRUNC('week',[Date Field]))-1

    END


    Comments:

    This works by checking whether the date field is an even or odd number of weeks from the referenced date. If it is an even number, then the week is the first week in the period and it is necessary to add 2 weeks the the date to find the period end date. If it is an odd week, you are one week from the period end and you need to add only 1 week to the date. Subtracting 1 gets you to the last day in the bi-weekly period. FYI, the percentage in the formula is the modulus operator. It divides by the number after it and returns the remainder.