Its been a while and I am back with some more useful info. Lately been spending a lot of time playing with enterprise oracle databases. Before going any further shout out to our favorite search engine Google.com without which last month and half would have been pure torture, but instead it was a fun self learning experience.
To start things off I’ll share some code to convert DATE TIME data from a database to WEEK ENDING dates. Over the past month the one thing I have done to all the market tracking dashboards that I created was introduce a week ending date based on the transaction date. This allows us to put in a realistic interval to watch market activities. A month is too long to wait and a day is just too short, making a weekly data snapshot perfect to support business decisions.
Initially I imported records to PowerPivot via an Oracle connection. Then used DAX calculated columns to find week-end dates. This is an imperfect solution if source data set is not updated daily.
For example, if days are captured like this Wednesday, Thursday, Friday, Sunday (no data on saturday) the DAX solution will show week ending as Friday (not Saturday, as it really should)
I am including it as there are still some uses to this technique, if you are working off a flat file (CSV, XLS, TXT, etc.) as your data source, this will help.
A column in the data set formatted as date. In this example date column is called [GIVEN_DATE]
Create a calculated column to find the week number:
Name the Column [GIVEN_DATE_WEEK]
You can make it a bit more user-friendly by adding the word Week as a prefix to the number
=”Week ” & WEEKNUM([GIVEN_DATE], 1)
Next to make it useful (no one knows week numbers, except first and last week of the year)
=”Week ” & WEEKNUM([GIVEN_DATE], 1) & ” (ending ” & FORMAT(CALCULATE(MAX([GIVEN_DATE]), ALLEXCEPT(‘WORKSHEET_NAME’,’WORKSHEET_NAME'[GIVEN_DATE_WEEK])), “YYYY-MM-DD”) & “)”
Output will look like this Week 1 (ending 2014-01-04)
The first part to add the week number is optional, format the string to your pleasure, remove the week number completely if you prefer it to just show something like Week Ending 2014-01-04 (my current preference).
Note: Change the 1 in WEEKNUM to 2 if you want the week to start on Monday instead of the Sunday.
Next up the same thing in ORACLE SQL
Yup, it’s that easy, add this in your SELECT statement and your done. This gives the date of the following Saturday (week ending)
To get the week start, drop the +6 and you get the date of the preceding Sunday.
Hopefully this has been helpful, and if you have a better way of doing this please share in comments.
Until next time,