# Calculate Week Commencing Date From Date Column



## knotty150 (Jul 10, 2014)

Hi There

I'm struggling with a date formula, should hopefully be quite easy to explain.  Basically all I'm aiming to achieve is a count of products sold within a week commencing group.  I've got 2 columns, one is date, one is product sold - how do I essentially group the 1 date column into a week commencing column?  e.g.:

*Date             | Week Commencing * 
10/07/2014  | 07/07/2014
11/07/2014  | 07/07/2014
12/07/2014  | 07/07/2014
13/07/2014  | 07/07/2014
14/07/2014  | 14/07/2014
15/07/2014  | 14/07/2014
15/07/2014  | 14/07/2014
15/07/2014  | 14/07/2014
16/07/2014  | 14/07/2014
17/07/2014  | 14/07/2014
18/07/2014  | 14/07/2014

Hope that makes sense!

Any advice is much appreciated.

Many Thanks

Rich


----------



## Tianbas (Jul 10, 2014)

I would build a data table in standard Excel with all dates of the year in the first column and a calculation for the week commencing in a second column. Then add it to the data model as linked table and build a relationship between your product sold table and the calendar table. Afterwards you should be able to group by week commencing in your pivot table.


----------



## scottsen (Jul 10, 2014)

What he said.

You are absolutely going to want a "real" date table.  This will have specific requirements, but isn't complex.  Basically, it has to have no gaps (don't skip weekends/holidays), and should cover all the dates you care about.   To it, you can add things like WeekCommencing, Quarter, DayNameOfWeek, etc.  After you relate the 2 tables, you will use rows from your CALENDAR table (not the fact table) on rows/columns/slicers to summarize your data.


----------



## knotty150 (Jul 11, 2014)

Morning Chaps

Thanks for your responses.  I actually finally managed to find a DAX solution late last night:

=Table[Field]-WEEKDAY(Table[Field],1)+1

This directly calculated the week commencing date from a date column.

I think your points about making a date table are definitely worthwhile and I'll most certainly be doing it (the above DAX formula being one of the field I intend to include).  Sounds like something which I'll undoubtedly be needing to use on a regular basis!

Thanks Again

Rich


----------

