Forum Discussion
Automating measures to update each week
Hi everyone,
I hope someone can help with a requirement I have with some measures in a report? I am fairly new to using Power BI so apologies if the language I use is not quite correct.
I have a table (Morpheus) where the column headings are dates (for example 03/05/2020). I then have 9 measures that each week I update manually to the correct date, for example "W5 = SUM(Morpheus [03/05/2020])" will become "W5 = SUM(Morpheus [10/05/2020])" next week. Is there a way to get Power BI to do this automatically?
Many thanks,
Danielle
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.
16 Replies
- AllisonKennedy
Community Champion
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.
- Greg_DecklerI agree with AllisonKennedy , you almost certainly want to unpivot those date columns. Should make things vastly easier.
- Greg_Deckler
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.
- amitchandak
Super User
Anonymous , I did not get it completely. You can create week rank based week no or week start date and use that to filter let us a current week or last week
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last 12 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-12 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Refer
Automatically it is impossible, a tabular format is created by a set of columns that have a name and this name cannot be repeated.
If you load the source data and the system cannot identify the column name, name it Column 1, Column 2, and so on.
Once the column name is assigned, the column cannot be changed automatically, and formulas are either DAX or M-language that reference a dynamic name column would fail.
