How to get Week Commencing

[Cantona_lives]

Hi all,
I am starting to learn about PowerPivot and the value it can provide, but I am hitting a few roadblocks that some kind person can possibly help me with.

So I have a SQL query that pulls in 'Name', 'Task' and 'Finish Date' from an external source.

I would like to be able to have 'Week commencing' running across the pivot table columns and then 'finish date' appearing in the value of the date within which it falls.

Something like this :

Week Commencing
Name Task Finish Date 29/12/2014 | 05/01/2015 | 12/01/2015
Test1 Task 1 01/01/2015 Yes No No

Of course a number or anything in the value section would work as I am attempting to create a timeline.


I have managed to pull all the week commencing in as a table, but getting them to link other than just as a date is the challenge. In other words, I can display dates, but no values. Therefore I am missing a vital piece of the relationship.

Hope that makes some level of sense?


Thanks in advance for any advice.


-CL

 

[Cantona_lives]

To qualify futher,

I could easily add dates as a manual entry and then use the 'AND' formula to calculate where x row has a date less than week commencing and more than week commencing, however using dates in this manner would make for purely manual updates of week commencing and would prevent filters by date.

 

[theBardd]

You could add a calculated column in the PP datamodel to get the Monday of the Finish Date,

W/c Date:=[Finish Date]-(weekday([Finish Date])-2)

and then in your pivot either use that as the column header and do a count of Finish date in the values, or use the dates from date table (if linked to your main table).

 

[Cantona_lives]

Thanks, that worked to a point.


I would like all week commencing to display regardless of whether they are populated or not - Is that achieveable?

So far I am seeing that if a week commencing does not have a date associated, it does not display.

-CL

 

[theBardd]

Yes, you would need to use the dates in your dates table as column headings, and then goto Pivottable Options>Display>Show items with no data on columns.

 

[Cantona_lives]

Yes had susequently located. Now just have a date issues with some dates pulling through time as well as date, despite the format being dd/mm/yyyy.

Thanks,

-CL

 

[scottsen]

You will want to nuke those times on their way out of SQL. cast(DateColumn as Date) as [Date] or whatever. If you want the times for other reasons... put it in a separate column.

 

[Cantona_lives]

All,
Thanks for your help. Got there in the end, the formula above really helped!



Kind regards,



-CL