Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ha...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

     

     
amitchandak's avatar
amitchandak
Super User
6 years ago

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

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