Results 1 to 5 of 5

Thread: PowerPivot Date not as Date in calc?

  1. #1
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0

    PowerPivot Date not as Date in calc?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    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))

  4. #4
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    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.

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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/downl....aspx?id=29074
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •