Dear Experts
I’m trying to calculate the % change for sales amount from week to week but it gives a wrong calculation when it reaches the week before end and after the year e.g. (2008) , please check the screenshot below:
I did important code to make the start week date as the following:
Week Start Date =
‘Date’[Date]-WEEKDAY(‘Date’[Date],2)
WeeknYear = ‘Date’[Year] * 100 + ‘Date’[Week Number]
Week Number = WEEKNUM(‘Date’[Date],2)
The problem is not In the calculation , It is on how to create a logic making the week number not repeated in a form of sequence over the dates to make sure the calculation will make calculate (Week - 1) , this will resolve the issue when the week reach the end of the year , thats why I’m asking your help.
I attached the resource with the post.
Note: I need the Week Start date to be displayed.
C0120 - Filter top 3 products with a row for others using DAX in Power BI -EDNA.pbix (2.0 MB)
would this fix your Week Number problem?
Modified WeekNum =
VAR _MinYear = MINX( ALL( Dates ), Dates[Year Number] )
VAR _YearDiff = Dates[Year Number] - _MinYear
VAR _NumWeeks = 53
VAR _AddAmt = _NumWeeks * _YearDiff
RETURN
Dates[Week Number] + _AddAmt
Hi @Heather
I did as the following:
1-Create the new column Modified WeekNum , with the code you mentioned.
2-Then I Modified
WeeknYear = /// ‘Dates’[Year] * 100 + ‘Dates’[Week Number]
‘Dates’[Year] * 100 + Dates[Modified WeekNum]
Still Nothing change , kindly can you do the recommended change on the file I attached it ?
Hi @MAAbdullah47,
If that is your only requirement, just change the logic for the year column in your Dates table, so it lines up with the Year of the Start of Week date.
I hope this is helpful
Hi @Melissa
In my previous post you put a very helpful parameter:
WeeknYear = ‘Dates’[Year] * 100 + ‘Dates’[Week Number]
This will be a unique and will act as a sequence , then when we implement it on the %Change :
WeeknYear it might be even beter than (Year Start of The week) , dispte that the reults in changing the year is not correct , what is the advantage of using (Year Start of the Week) than (WeeknYear) ?
What sort of changes shall we do in the filter of (W&W Cahnge)?
Hi @MAAbdullah47,
You need to create a value that remains constant for 7 days.
If you want to work of the Start of Week that’s fine just caluclate the Weeknum and Year for that Start of Week Date for each Date and combine those in a WeeknYear value. Then apply that logic elsewhere when needed.
Thank you @Melissa , Kindly can you show it to me on the Pbix file or on the Measure Name you target it ?
Hi @MAAbdullah47,
All steps for your Dates table
.
and your measure
I hope this is helpful
1 Like
Thank you So Much @Melissa it works , but give me one more chance to practice it on more than one applications to make sure it is 100% valid.
Hi @MAAbdullah47, following up on this. 
Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @MAAbdullah47, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
