Forum Discussion
sconway1985
Sep 27, 2021Brass Contributor
Using GETPIVOTDATA To fill separate sheet based on dynamic data tied to dates
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 t...
- Sep 28, 2021SergeiBaklan
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
SergeiBaklan
Sep 27, 2021MVP
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 Chris Webb's BI Blog (crossjoin.co.uk) but that's for insiders.
sconway1985
Sep 28, 2021Brass Contributor
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.
- SergeiBaklanSep 28, 2021MVP
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.
- sconway1985Sep 28, 2021Brass ContributorI'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.
- sconway1985Sep 28, 2021Brass ContributorSergeiBaklan
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