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.
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.
