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'):
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???
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
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.
Would you like to participate?