My client has a reporting week that starts on a Monday and ends on a Sunday. I have a date field that I'd like to use as a column for a line graph. I need the horizontal axis to display each week ending date (each Sunday: 9/23/12, 9/30/12, 10/7/12) and the data points should be totals/averages for the data from Monday-Sunday (the 10/7 data point would have data from 10/1/12 12:00 AM - 10/7/12 11:59 PM).
The data is technically in 2 different weeks per the normal calendar (Mon. - Sat. of week 1 + Sunday of week 2), but I want to define my own weekly structure. Any help would be greatly appreciated.
Hi Michael,
You could create a calculated field as a dimension to do this.
Might get a little dicey if you involve multiple years, but should work for a single year at least...
Week Name:
IF Datename('weekday',[ETLDate]) = 'Sunday'
THEN dateadd('week',datepart('week',[ETLDate])-1,#1/1/2012#)
ELSE dateadd('week',datepart('week',[ETLDate]),#1/1/2012#)
END
//note that [ETLDate] was the date field from my data
returns this:
Hope this helps!
