Calculated fields with dynamic field names

KarenXL

New Member
Joined
May 24, 2019
Messages
6
I have a project that requires calculated fields (I think) and the field names change throughout the year. I am struggling with how to set this up. I am creating the dataset using power query. The resulting data is below. I will need to report this information the way it is shown below, as well as summarized by region. I am assuming that I need to create a calculated field in a pivot table or power pivot so that the occupancy % calculates properly. I cannot just average the occupancy percentage per community as the roll-up calculations would not reflect the different community sizes. My problem is that when I create the calculated field I am using column names that will change each month. The end users want to see those month names, and not "Current month", "Prior Month", "Month before that".

My intention is that the data will be refreshed daily via power automate. The queries are pulling the data from daily emailed reports.

I would appreciate any advice on this. At the moment my thinking is to bring in the data with with generic column names and then have a "shadow sheet" that changes the column names to be date-specific and run the pivot table from there. This seems really clunky as I am duplicating the table.
1705328043023.png
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top