topic Re: W&W Change giving wrong calculation when shift to next year in DAX Commands and Tips

W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Tue, 29 Jun 2021 08:12:27 GMT

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)

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

Re: W&W Change giving wrong calculation when shift to next year

Anonymous — Tue, 29 Jun 2021 11:00:31 GMT

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Wed, 30 Jun 2021 09:58:58 GMT

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?

Re: W&W Change giving wrong calculation when shift to next year

Anonymous — Wed, 30 Jun 2021 13:41:54 GMT

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.

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Wed, 30 Jun 2021 12:53:30 GMT

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) :

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Wed, 30 Jun 2021 12:59:32 GMT

@ 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 ).

Re: W&W Change giving wrong calculation when shift to next year

Anonymous — Wed, 30 Jun 2021 13:41:20 GMT

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 )

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Wed, 30 Jun 2021 14:56:02 GMT

hi Anonymous
It gives an error Kindly chceck:

Re: W&W Change giving wrong calculation when shift to next year

Anonymous — Wed, 30 Jun 2021 15:27:41 GMT

It's because you have not written it right. You've got too many brackets.

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Wed, 30 Jun 2021 16:14:03 GMT

Ok I'll check and get back to you.

Re: W&W Change giving wrong calculation when shift to next year

MAAbdullah_47 — Thu, 01 Jul 2021 08:08:04 GMT

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?

Re: W&W Change giving wrong calculation when shift to next year

Anonymous — Thu, 01 Jul 2021 09:52:44 GMT

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.