Automating measures to update each week

[d-peyton_86]

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

[Guiseppe]

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.

[amitchandak]

@d-peyton_86 , 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

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[AllisonKennedy]

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. 

https://sites.google.com/site/allisonkennedycv

[d-peyton_86]

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?

[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.

[d-peyton_86]

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.

[Greg_Deckler]

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.

[d-peyton_86]

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.

[v-kelly-msft]

Hi @d-peyton_86 ,

Can you upload part of your data and expected output to make your requirement more clear?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

Right, so your first measure would be:

W5 = SUMX(FILTER('Morpheus',[Attribute] = DATE(2020,03,05)),[Value])

Better, create a table like:

Week,Date

W5,3/5/2020

W6,...

[d-peyton_86]

This is similar to the approach I've now taken with a formula in excel that changes each week based on today's date.

Thanks for your help everyone, I think the fact that the columns are all headed with dates has confused the siutation.  I'm familar with unpivotting/pivotting columns and this would not have solved the situation as there are 52 columns to unpivot.

Kind regards,

Danielle

[AllisonKennedy]

@d-peyton_86  Sorry, what was the solution you have used?

---

@d-peyton_86 wrote:

This is similar to the approach I've now taken with a formula in excel that changes each week based on today's date.

Thanks for your help everyone, I think the fact that the columns are all headed with dates has confused the siutation.  I'm familar with unpivotting/pivotting columns and this would not have solved the situation as there are 52 columns to unpivot.

Kind regards,

Danielle

---

I'm curious to hear what you've done, because I'm still confused why you can't just unpivot and then use DAX to calculate ThisWeek and PreviousWeek and %Diff that will be much more efficient than working with 52 columns.

Unpivoting 52 columns is possible... 

Look forward to hear what you've done.

[d-peyton_86 · Accepted solution]

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.

[v-kelly-msft]

Hi @d-peyton_86 ，

Is your issue solved?If so,can you mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[AllisonKennedy]

This might help explain what we mean about the unpivoting: 

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

@d-peyton_86  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. 

https://sites.google.com/site/allisonkennedycv

[Greg_Deckler]

I agree with @AllisonKennedy , you almost certainly want to unpivot those date columns. Should make things vastly easier.