getpivotdata to fill out table

[RaymondP2255]

I am trying to use the numbers on the right as a key to get the value from the matching "Dinstinct Count of Rma Number" in the pivot table. I am not sure about how to use this function and the videos I have watched have not helped. I also want it to output a zero, but I should be able to do that with an isna()

Here are the pivot table fields if that helps.

Any help would be appreciated.

[#2]

@RaymondP2255 

For such setup

it could be

=IFERROR( GETPIVOTDATA("[Measures].[Distinct Count of RMA Number]",$G$3,"[Table1].[Day]","[Table1].[Day].&[" & [@Day] & "]"), 0 )

[#3]

@SergeiBaklan 

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.

[#4]

@RaymondP2255 

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?

[#5]

@RaymondP2255 

Oops, as you use Distinct Count you use data model, this question is closed.

[#6]

@SergeiBaklan Here is the link:

SharedRMAReceived.xlsx

[#7]

@RaymondP2255 

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

[#8]

@RaymondP2255 

Got your file, thank you. Will repeat on it.

[#9]

@RaymondP2255 

We need to add DAY column to data model

and in formula it shall be not $B$4

but $B$2, i.e. reference on Row labels cell

Please check attached

[#10]

@RaymondP2255 

More complex variant if to use other cube formulae in combination with dynamic arrays

=LET(
 daysSet,        CUBESET("ThisWorkbookDataModel","[Table4].[Day].children"),
 SelectedMonths, CUBESET("ThisWorkbookDataModel", Slicer_Date__Month1),
 SelectedYears,  CUBESET("ThisWorkbookDataModel", Slicer_Date__Year1),
 DistinctCount,  CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Distinct Count of Rma Number]"),
 daysList,       CUBERANKEDMEMBER("ThisWorkbookDataModel", daysSet, SEQUENCE(31)),
 AllCounts,      MAP( daysList, LAMBDA(v,
                     CUBEVALUE("ThisWorkbookDataModel",
                               DistinctCount,SelectedMonths,
                               SelectedYears,
                               v)
                 ) ),
 VSTACK(
   {"Day","RMA Distinct Count"},
   FILTER( HSTACK( daysList, AllCounts), AllCounts<>"") )
)

That formula gives

see in Model sheet