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.
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.
This might help explain what we mean about the unpivoting:
Anonymous Power BI is designed to operate on 'database' style data, so once you unpivot the data, we can help you with the required DAX to get what you want, but as you say, you are new so a bit of study on DAX context might be helpful to make your life easier.
UPDATED TO ADD: Please try to unpivot the data, as once you do this, you'll discover that you have a whole new set of possibilities and can actually report on this week compared to last week much easier.
After unpivoting the dates, you'll have an 'attribute' and 'values' columns. Rename the 'attribute' to [Date]. Then while still in Power Query Editor you can use the Add Column tab: Select the newly create [Date] column, click the Date button and choose 'Week of Year'. This will give you the information you need to get the weekly totals.
Then back in the report, just put [Week of Year] in a matrix in rows and put your column for hours in values.
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.