Using GETPIVOTDATA To fill separate sheet based on dynamic data tied to dates

[sconway1985]

Hello,

I have been attempting to get my data in a table to correspond with the dynamic data in a pivot table that is on a separate sheet within the same workbook.

The problem is I can't get the data in the table to correspond with the coinciding dates in the pivot table except for the first initial date.

What would I need to input into my formula to make this work? Hypothetically these tables will continue until the end of time and will be updating every day.

Thanks in advance,

Sconway   

[#2]

@sconway1985 

Not sure I understood the question. If what to use here

when B14, B15, etc (reference on the cell in Date column).

[#3]

Hello Sergei,
Yes I think that end of the formula is what I need to edit. I need to reference not just that specific date with the formula. I need to have a formula that will autofill the remainder of the days.

[#4]

@sconway1985 

If you mean to repeat automatically calculations for all dates presented in PivotTable that's, I guess, with data model. For that first add data to data model creating creating PivotTable.

One way is generate DAX query for needed calculations with  ADDCOLUMNS and / or SUMMARIZE Best Practices Using SUMMARIZE and ADDCOLUMNS - SQLBI , add this query to linked-back table Linkback Tables in PowerPivot for Excel 2013 - SQLBI . With that you have structured table within the grid based on data model table.

More modern way is as here Chris Webb's BI Blog: Excel Cube Functions, Dynamic Arrays And Lambdas, Part 2: Returning Tables Chr... but that's for insiders.

[#5]

@Sergei Baklan 

I'm not sure I understand what you mean. 

The data itself is input via PowerApp that feeds a SharePoint list. This list is then fed as raw data into a sheet within the book automatically and all I am doing is referencing the raw data to create the Pivot Table. Then I am referencing that Pivot Table from another sheet to create another table.

[#6]

@sconway1985 

I see, but in general data source doesn't matter. Based on it you may build "legacy" PivotTable which keeps data in cache, or build tabular data data model and aggregate data based on it. The latest is much more powerful - and more complex of course.

[#7]

I'm just trying to figure out what I need to do with the provided formula to make it continue to reference the pivot table's consecutive dates and fields associated with them.

[#8 · Accepted solution]

@Sergei Baklan

I figured it out
=GETPIVOTDATA("Min of Effluent_x0020_Meter_x0020_Start",Sys.PVOps!$B$3,"Date1_asdate",[@Date])
allows me to perform the function I wanted.

Thanks for your help

Best

[#9]

@sconway1985 

Great to know, thank you for the feedback. I misunderstood you - I though you'd like to use dates range returned by PivotTables, not predefined manually dates in column.