Calculate Week Commencing Date From Date Column

[knotty150]

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]

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]

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]

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