Sep 27 2021 02:56 PM
Sep 27 2021 02:56 PM
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,
Sep 27 2021 03:30 PM
Not sure I understood the question. If what to use here
when B14, B15, etc (reference on the cell in Date column).
Sep 27 2021 03:44 PM
Sep 27 2021 04:02 PM
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.
Sep 28 2021 06:45 AM
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.
Sep 28 2021 07:51 AM
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.
Sep 28 2021 08:05 AM
Sep 28 2021 09:38 AMSolution
Sep 28 2021 12:20 PM
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.