Mike_Alex
New member
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.
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.