PowerPivot Date not as Date in calc?

Hope this makes sense, if not, please let me know (and yes, I know you will)

I have had an outstanding issue with PowerPivot, and have come to the conclusion that it is trim to throw it to the experts. My goal is to create a dashboard that will require the user not to have to select a date, but would rather just pull in last week ending date when report is run. Normally, I use OLAP cubes, so I just use cuberankedmember, and then take (Today()-Weekday()) to get last week ending date, and match that to the cube so last week ending date is last date on charts. For prior dates, I just take 7 from the date, and get the week prior, prior again, and so forth. After this, I use in a cubevalue formula to get the data needed for the chart, either by a data validation or hard coded in.

Scenario works well, but when using PowerPivot, it returns the date as 2014-01-31T00:00:00. I would think I could just concatenate the date pulled apart and then add the time (such as 2014&01&31&”T00:00:00”). However, this does not work in the cube formula (such as =cubevalue(“mydata”,”[Measure]”,”[Date].[“&(formula for date and time)&”]”).

Question for you experts, is can this be done? Can you pull a formula together other than concatenate, that will make PowerPivot see a regular date as one in PowerPivot so the user always gets the last week ending dates data?

Thank you in advance for your advice.

Using:
&TEXT(cell,"yyyy-mm-ddThh:mm:ss")&

doesn't work? That should format it correctly.

Have you thought about adding a column to your data table to calculate if the date is in your current range and return "Current" (or something similar)? Then you could easily filter to just show those records...

Sorry stuck on 2010 so PowerPivot an unknown for me. Apologies if below is not applicable.

I'm forever having to deal with dates formatted as text. In addition to the middle T there is the month/day order. I would be tempted to use something like
= date(left(cell,4), mid(cell,6,2), mid(cell,9,2)) + timevalue(right(cell,8))

Ken, you are a bloody genius. Solution worked fine, and when I used it inside the formula, [Week Ending].&["&(TEXT((TODAY()-WEEKDAY(TODAY())),"yyyy-mm-ddThh:mm:ss")))&"]"), it worked like a charm allowing me to always reference last week ending. Wizard, yours did not actually work. Came back with a #NUM error. However, I do thank you for the attempt. I also thank you both for such a quick professional response.

Glad it worked for you.

Wizard, the format you built would work to decompile a text date into a date/time, but when we're feeding into a CUBE formula we need to feed it in as text. (We're going the other way.)

Regarding your comment on Excel 2010... you can download PowerPivot for free to bolt on. You need admin rights to install, but so long as you have that, I'd HIGHLY recommend it. It can guarantee it will change your life if you spend some time with it. Just make sure you follow install the .NET Framework 4.0 AND the Visual Studio Runtime first, as you'll need those. Links can be found in the Install section on the download page: http://www.microsoft.com/en-us/download/details.aspx?id=29074