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.
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.
Still nearly positive you need to unpivot those columns. But, this is where you need to refer to please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Sample data, expected result, formulas if you have them, a little context of how your visuals are laid out never hurts.
Hi again, I'm not exactly sure what you need so hope this helps:
Morpheus is table containing a list of stores and by date how many hours they receive, therefore we create a sum of each column.
W5 = SUM(Morpheus[03/05/2020])
5 is table containing a list of stores and how many hours they have used in a specific week, therefore we create a sum of this column.
5 Demand = SUM('5'[5])
We then need the difference between the two tables.
5 VH = [5 Demand] - [W5]
We then need the % difference between the two tables.
5 V% = DIVIDE('5'[5 VH], [W5])
All I need to be able to do is avoid a manual process of changing the column summed in the first measure. The fact the column is named as a date is just a coincidence.