Last Period to Date and Period to Date

Version 1

    Description:

    This is a multi-step process, which include 2 calculated fields and a parameter. Essentially this will allow you to create a single Viz, that shows Period to Date and Last Period to Date, while allowing the user to select the period.

     

    Use Case:

    You are asked to build a single view that compares Period to Date and Last Period to Date. The user wants the ability to select which period they want to compare, i.e Year, Quarter and Month.

     

    Example Calculations and Steps:

     

    1. Create a string parameter like the following.

     

    5-15-2015 2-30-04 PM.png

     

    Very important, the Value MUST be lower case!

     

    2. Create the Calc that will filter your dates.

     

    (DATETRUNC([Period], [Date]) = DATEADD([Period], -1, DATETRUNC([Period], TODAY()))

        AND

    DATETRUNC('day', [Date]) <= DATEADD([Period], -1, DATETRUNC('day', TODAY())))

    OR

    (DATETRUNC([Period], [Date]) = DATETRUNC([Period], TODAY())

        AND

    DATETRUNC('day', [Date]) <= DATETRUNC('day', TODAY()))

     

    3. Create a calc that will be used for Date dimension.

     

    DATETRUNC([Period], [Date])

     

    4. When you drag the Date Dimension calc onto your Viz, select Day (Continuous)