topic Re: Week over Week comparison by country in DAX Commands and Tips

Week over Week comparison by country

Anonymous — Thu, 27 Jan 2022 14:47:28 GMT

Hi All,

I have a weekly sales measure and a Week on Week measure that calculates the change of sales over week under the same table.

On a total sales aspect it works fine.

When I add the Country it takes sales of week 2 for the specific country and subtracts it from total sales of week 1.

So for UK&I it takes week 2 sales 115784 and subtracts from total group sales of week one, 2,501,074.

What do I have to change to obtain weekly difference by country?

Many thanks

Re: Week over Week comparison by country

Anonymous — Thu, 27 Jan 2022 15:09:24 GMT

hi.there you need to calculate week ranks insted of week measure but i am not sure

Re: Week over Week comparison by country

amitchandak — Thu, 27 Jan 2022 15:10:01 GMT

Anonymous , For that, is better to have a separate date/week table with week rank on year week. In same table all will remove other filter from the context

new column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

Re: Week over Week comparison by country

ValtteriN — Thu, 27 Jan 2022 15:15:10 GMT

Hi,

I recommend cheking this post about a similar topic:
https://community.powerbi.com/t5/Desktop/Week-over-Week-Change/m-p/243318

The Week over week calculations presented there should solve your issue.

Re: Week over Week comparison by country

VahidDM — Thu, 27 Jan 2022 21:47:37 GMT

Hi Anonymous

Check this post: https://www.vahiddm.com/post/weekly-time-intelligence-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Week over Week comparison by country

Anonymous — Fri, 28 Jan 2022 16:23:46 GMT

Hi amitchandak Thanks for the info provided. Unfortunately the Last Week/ This Week measures add up ALL of the sales as per screenshot below:

In my original script I do use remove filters[nvoice Date] and in the SUMX of Sales and i do get correct weekly sales. It is the delta between week one and week two that does not work.

Happy to share a sample so you have a clearer picture.

The week Rank though seems to work correctly: