Forum Discussion
Automating measures to update each week
The solution is excel based in the original file. The column headers now update each week to the correct reference, for example WC 10/05 is currently called "5" but next week WC 17/05 will be called "5". This means the measure is "Week 5 = SUM([5]) so I no longer need to update the measures manually.
I would potentially unpivot the data in the Power Query Editor first so that the date column headings become values, and then use DAX time intelligence functions and TODAY() to dynamically filter and update the measures each week.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
- I agree with AllisonKennedy , you almost certainly want to unpivot those date columns. Should make things vastly easier.
Thanks for your reply. The reason we use the date has the column header is because we need a sum of the data contained in each column. Will your solution still work?
Sure, you would just put your resulting "Attribute" column containing the dates in a table. You can rename the column. This will give you a distinct list of dates. Then put your "Value" column and use a default aggregation. No measures. Vastly easier.
I'm sorry I'm not sure what you mean. I need the measures to create a SUM that can be used to create a % difference later in the calculations.
