Forum Discussion
getpivotdata to fill out table
For such setup
it could be
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of RMA Number]",$G$3,"[Table1].[Day]","[Table1].[Day].&[" & [@Day] & "]"), 0 )
I am trying to replicate what you did for your table and for some reason my pivot table is outputting the date like this
My data looks like this:
Also my table isn't pulling the numbers when using the code you gave.
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of Rma Number]",$B$4,"[Table4].[Day]","[Table4].[Day].&[" & [@Day] & "]"), 0 )
Any ideas as to what I am doing wrong?
I also can't seem to attach my excel file.
Better to discuss this with the file. You may put it on OneDrive, Google Drive, whatever, share to everyone and post the link here.
Also on which Excel platform/version you are and add you data to data model creating PivotTable or not?
Oops, as you use Distinct Count you use data model, this question is closed.
Got your file, thank you. Will repeat on it.
Step by step
- we add PivotTable as
In Power Pivot add column with Days
Be sure this setting in Options is On
Create table with all possible days
Within table in any cell start from = and click on any cell in Distinct Count of PivotTable
Enter. Formula like
=GETPIVOTDATA("[Measures].[Distinct Count of Rma number]",$H$6,"[Table1].[Day]","[Table1].[Day].&[12]")appears.
Now let modify this
part of it on
Wrap formula with IFERROR, finally
=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of Rma number]",$H$6,"[Table1].[Day]","[Table1].[Day].&[" & [@Day] & "]"), 0 )In Power Pivot add Year and Month columns if they were not created initially
=YEAR(Table1[Date]) =FORMAT(Table1[Date], "mmm" )Add slicers or filters to PivotTable
Here we are
