DAX - Find values between date ranges


  • Hi all,

    I'm trying to create a calculated column [Work Quarter] that demarcates which (work-related) quarter for individual employees, based on unique dates of our employment calendar.

    Here's my table ('Work'):

    ID Date Worked Work Quarter
    John 3/11/2014 Q1-14
    Mark 4/20/2014 Q2-14

    Q1-14 dates are 1/12/2014 through 4/12/2014.  (John worked on a date within this range.  Therefore, "Q1-14.")

    Q2-14 dates are 4/13/2014 through 7/14/2014.  (Mark worked on a date within this range.  Therefore, "Q2-14.")

    I've tried using nested IF statements to create the [Work Quarter] column, but keep getting errors.  Any suggestions???



    Tuesday, May 06, 2014 5:42 PM


  • Hi,

    It's good practice to have a dedicated 'calendar' table in your model that has a column for unique, sequential dates through your data period with no gaps.

    You then create a relationship between this table and your work table and you can then slice the work table by the additional columns in the calendar table, one of which can be a 'Quarter' column.  

    Your Calendar table can be a simple Excel table that you create yourself therefore no complex formulas are required.

    You can include dates in an IF() in a Calc column but in the recent versions of PP I've found you've had to use the number rather than the date (e.g. 41740 for 1st of May).



    Jacob | Please mark helpful posts and answers

    • Marked as answer by undergrads1 Wednesday, May 07, 2014 5:22 PM
    Tuesday, May 06, 2014 8:34 PM