Forum Discussion
Same week previous month increase in sales.
Hi Experts
I would like to calculate the Percentage increase in same for the same week previous month
So compair Feb 21 week 1 with Jan 21 Week 1 and so the percentage increase or decrease in sale...
sample file
https://www.dropbox.com/s/8sr3gasjfxa6tbl/2021%200330.pbix?dl=0
Hi, Anonymous
In my opinion, it would be a bit easier if you can add WeekOfMonth number column in Power Query Editor.
It can be added by one or two clicks.
I created the column and please check the below picture if that is what you are looking for.
https://www.dropbox.com/s/54joh9bod275lml/2021%200330.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
7 Replies
- Jihwan_Kim
Super User
Hi, Anonymous
In my opinion, it would be a bit easier if you can add WeekOfMonth number column in Power Query Editor.
It can be added by one or two clicks.
I created the column and please check the below picture if that is what you are looking for.
https://www.dropbox.com/s/54joh9bod275lml/2021%200330.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
- Jihwan_Kim
Hi, Anonymous
Please check the below.
total sales same week# previous month =
VAR currentyear =
MAX ( Dates[Year] )
VAR currentmonth =
MAX ( Dates[MonthNo] )
VAR currentweeknumber =
MAX ( Dates[Week of Month] )
RETURN
CALCULATE (
[total sales],
FILTER (
ALL ( Dates ),
Dates[Year] = currentyear
&& Dates[MonthNo] = currentmonth - 1
&& Dates[Week of Month] = currentweeknumber
)
)Percentage diff =
DIVIDE (
[total sales] - [total sales same week# previous month],
[total sales same week# previous month],
BLANK ()
)Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
- amitchandak
Anonymous , You need to create followind columns in date table
Start Month = STARTOMONTH('Date'[Date])
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1Month Rank = RANKX(all('Period'),'Period'[Start Month ],,ASC,Dense)
Measures like
YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank ]=max('Date'[Month Rank ]) && 'Date'[Month Week] = Max('Date'[Month Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank ]=max('Date'[Month Rank ])-1 && 'Date'[Month Week] = Max('Date'[Month Week])))
