Forum Discussion
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 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
- SergeiBaklan
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
Not sure I understood the question. If what to use here
when B14, B15, etc (reference on the cell in Date column).
- 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.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.
