GetPivotData and Time Values as Cell References

[kheldar]

Hello,

I seriously think that this is a bug. Actually maybe more than one bug.

First;

Using regular data model, if the source data includes a time value such as 00:00, you can't display those fields as times. 00:00 appears as 00 and changing number format doesn't do anything. In addition to this, you can't make a cell reference to use getpivotdata using a time value, let's says my cell a3 is 00:00 and I want to get that time's value but I can't reference it using a3. you have to hard code it as "0" and this kills the purpose because you can't flashfill this way.

Second;

Using Excel's Data Model,

You can get the pivot to display the time values correctly such as 00:00. However, as you know using getpivotdata with Excel's Data Model, things get complicated and again you can't reference a cell as a time value because excel reads the time as "1899-12-30T00:00:00" and referenced cell doesn't match this value.

Are these really bugs? If so how can I report them? Is there anyway around this?

[#2 · Accepted solution]

@kheldar 

I'd avoid using of implicit measures and GETPIVOTDATA(). For such sample

we may create explicit measure

Values sum:=SUM( [Value] )

and return related value by

=CUBEVALUE( "ThisWorkbookDataModel",
       CUBEMEMBER( "ThisWorkbookDataModel", "[Table1].[Time].[" & TEXT( $K$4, "hh:mm" ) & "]"),
       CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Values sum]") )

In the sample above time is selected from drop down list, please see attached.

[#3]

Hmm I just learnt cube functions from your post and doing research on them. Can you use them without power pivot?

=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[PivotTable4].[Hours].["& TEXT(A3, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[SLA]") )

This formula returns #N/A the pivot's name is PivotTable4 the field to look for is Hours and the value is SLA. The pivot is in another sheet. What am i doing wrong?

[#4]

@kheldar 

In general yes, Power Pivot is only interface to data model. In this case to use implicit measures or create explicit measures by right click on table name

In attached file same formula with implicit measure, it works.

[#5]

@Sergei Baklan 

This is the layout

=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[PivotTable1].[Hour Formula].["& TEXT(A3, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[SLA]") )

Why do you think I still get #N/A ?

[#6]

@kheldar 

It's hard to say without the file. What is PivotTable1, is it generated automatically when you start typing "[ , or you added it manually? In general it shall be name of the table which is source for PivotTable, not the name of the PivotTable.

[#7]

Thank you! I managed to get it done by changing it to table name! You are super helpful. I can't thank you enough!

[#8]

@kheldar , you are welcome