Calculate Week Ending in DAX and SQL


Hey all,

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.

Required:
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]

=WEEKNUM([GIVEN_DATE], 1)

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
TRUNC(GIVEN_DATE,’D’)+6

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,

namaste.

Advertisements

4 thoughts on “Calculate Week Ending in DAX and SQL

  1. “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)”

    That is why it is a good practise to have a separate Dates table in the model containing all the dates for the appropriate date range. You can add your calculated columns for month, year, or other custom periods to this table.

    A separate date table is also a requirement in Power Pivot for the built-in date functions to work.

  2. Thanks for your snippet. It helped me very fine.
    One thought though:
    If you have more than just one year in your Table/Dimension, you need to modify the DAX-Query a bit so that you get the right Week ending.
    =”Week ” & WEEKNUM([GIVEN_DATE], 1) & ” (ending ” & FORMAT(CALCULATE(MAX([GIVEN_DATE]), ALLEXCEPT(‘WORKSHEET_NAME’,’WORKSHEET_NAME'[GIVEN_DATE_WEEK],’WORKSHEET_NAME'[GIVEN_DATE_YEAR])), “YYYY-MM-DD”) & “)”
    You need to apply the year in order to get the right ending date of the week. Otherwise the Query will get the ending weekdate of the max year.
    So for multiple years you need to apply the year column in the ALLEXCEPT.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s