The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
I shared the PBix file please refer to report name (W&W change).
https://drive.google.com/file/d/1FVzwt7HIWz_eeAnf78WxAn8raQoNCo4o/view?usp=sharing
Thank you
Solved! Go to Solution.
All your weeks must be numbered starting from 1 up to the number of weeks you have in your calendar. Of course, I hope you realize that some weeks will belong to one particular year but will span 2 years? To make week calculations, you have to number them and then use this numbering to move back and forth in time in units of weeks. Since the standard weeks do not go evenly into years, quarters and months, you should never use any other periods together with weeks unless you know how to interpret the results (intersection of the week and the other period(s)). For serious week calculations you should use the special calendars like ISOWeek.
However, if you implement the numbering as I said above then it's easy to calculate the [Change Wow %]:
// 'Calendar'[WeekID] is an int
// column that consecutively numbers
// the weeks starting with 1.
[Change WoW %] =
var OnlyOneFullWeekVisible = true()
&& HASONEVALUE( 'Calendar'[WeekId] )
&& COUNTROWS( 'Calendar' ) = 7
return
if ( OnlyOneFullWeekVisible,
var CurrentWeek =
SELECTEDVALUE( 'Calendar'[WeekID] )
var CurrentValue = [Base Measure]
var LastWeekValue =
CALCULATE(
[Base Measure],
'Calendar'[WeekID] = CurrentWeek - 1,
REMOVEFILTERS( 'Calendar' )
)
var WoWChange =
DIVIDE(
CurrentValue - LastWeekValue,
LastWeekValue
)
return
WoWChange
)
My formula is correct. If you get something wrong, then it's the model you have that's faulty. I can't view your file (being behind my company's firewall), so this is all I can tell you. Sorry.
Hi @Anonymous
Please accept my apology for annoying you about my questions , from caclulation perspective I think it is has problem still when it goes from year to another year.
But how we sort the data based on what field , what column we can select
to sort the data based on it?
Hi @Anonymous I looked at it but In particuler my case I got confused , Kindly can you guide me very simply which of this article related exactly to my specific case?
You should implement week-related calculations exactly as the article says. Forget about your own method. Do it properly just the way the gurus, Marco and Alberto, say.
@Anonymous ok I adopeted it How I put the Year+Week Number inside a slicer?
Everything with Hirarchy , How I build the Hirearcy ? I want to do a slicer with (Year+Week Number) , Please check the sample screen-shot below sorted based on (Year & Week) :
@ I put slicer with this numbering (I created calculated column) to display the year and week number , The calclation is the same without any changing and the Date setting is same as (Marco and Albert) setting but the calculation result will come wrong If I put (Year & Week ).
All your weeks must be numbered starting from 1 up to the number of weeks you have in your calendar. Of course, I hope you realize that some weeks will belong to one particular year but will span 2 years? To make week calculations, you have to number them and then use this numbering to move back and forth in time in units of weeks. Since the standard weeks do not go evenly into years, quarters and months, you should never use any other periods together with weeks unless you know how to interpret the results (intersection of the week and the other period(s)). For serious week calculations you should use the special calendars like ISOWeek.
However, if you implement the numbering as I said above then it's easy to calculate the [Change Wow %]:
// 'Calendar'[WeekID] is an int
// column that consecutively numbers
// the weeks starting with 1.
[Change WoW %] =
var OnlyOneFullWeekVisible = true()
&& HASONEVALUE( 'Calendar'[WeekId] )
&& COUNTROWS( 'Calendar' ) = 7
return
if ( OnlyOneFullWeekVisible,
var CurrentWeek =
SELECTEDVALUE( 'Calendar'[WeekID] )
var CurrentValue = [Base Measure]
var LastWeekValue =
CALCULATE(
[Base Measure],
'Calendar'[WeekID] = CurrentWeek - 1,
REMOVEFILTERS( 'Calendar' )
)
var WoWChange =
DIVIDE(
CurrentValue - LastWeekValue,
LastWeekValue
)
return
WoWChange
)
hi @Anonymous
It gives an error Kindly chceck:
It's because you have not written it right. You've got too many brackets.
Ok I'll check and get back to you.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |