How to create a measure that dynamically updates

[JackFrench11]

I organized my data in power query and used power pivot to create a pivot table off that data model. I have the years 2019-2023 as columns in this current dataset. However, sometimes when I refresh, a 2024 column will be added. How do I create a new measure column that will take the sum of columns 2019:2023 as well as 2019:2024 without me having to manually edit the formula to include the new column?

Additionally, I have metrics that I will be conditionally formatting this table on. For example, I will want all values less than 100,000 filled green. How do I make this dynamic so that it works with the occasional inclusion of another column (2024)? I've tried but when I select 2019:2023 as my value range, it hard codes the formula and does not apply to the new column.

[#2 · Accepted solution]

@JackFrench11 You asked the same question a few days ago, and the confirmed that you had already pivoted the data in PQ and then built a pivot table on that data. Can't tell why there was no more follow-up in that thread.

But, I suggest you undo the pivoting in PQ so that you have a long list of 'audit events', including one column for the year. Then, a regular pivot table will automatically include columns in case New Years are added to the data. And the Grand Totals will be updates as well. No need for Power Pivot and DAX measures for that.

Regarding the conditional formatting, yes that may be a problem. Can't offer you a neat solution right now.