week on week comparison

[monojchakrab]

Hiya All,

I am trying to do a same week last month and previous week same month comparison.

Is there any function to do that or a guide to a measure which can help me do that?

I have a data table (attached), which has the weeknumber for every month with the date ranges (e.g. 1-7th is week 1, 8th-14th is week 2 and so on).

Date	YEAR	MONTH NAME	MONTH NUMBER	DAY NAME	WEEK NO
27-02-2022	2022	Feb	2	Sun	4
28-02-2022	2022	Feb	2	Mon	4
01-03-2022	2022	Mar	3	Tue	1
02-03-2022	2022	Mar	3	Wed	1
03-03-2022	2022	Mar	3	Thu	1
04-03-2022	2022	Mar	3	Fri	1
05-03-2022	2022	Mar	3	Sat	1
06-03-2022	2022	Mar	3	Sun	1
07-03-2022	2022	Mar	3	Mon	1
08-03-2022	2022	Mar	3	Tue	2
09-03-2022	2022	Mar	3	Wed	2
10-03-2022	2022	Mar	3	Thu	2
11-03-2022	2022	Mar	3	Fri	2
12-03-2022	2022	Mar	3	Sat	2
13-03-2022	2022	Mar	3	Sun	2
14-03-2022	2022	Mar	3	Mon	2
15-03-2022	2022	Mar	3	Tue	3
16-03-2022	2022	Mar	3	Wed	3
17-03-2022	2022	Mar	3	Thu	3
18-03-2022	2022	Mar	3	Fri	3
19-03-2022	2022	Mar	3	Sat	3
20-03-2022	2022	Mar	3	Sun	3
21-03-2022	2022	Mar	3	Mon	3
22-03-2022	2022	Mar	3	Tue	4

Thanks

[monojchakrab]

@amitchandak , Amit, this is partly solving the problem...

What I am looking to solve is as follows :

If I have the table like this :

Apr : wk1...wk4//May : wk1...wk4

So I want to compare two columns of data as follows :

1. May : wk4/wk3;wk3/wk2;wk2/wk1 &

2. Sales for same week last month :

May wk1/Apr wk1//May wk2/Apr wk2//May wk3/Apr wk3//May wk4/Apr wk4

That does not seem to be possible with this code...

Note : I have added the columns in the date table as required as per this code 

[monojchakrab]

@amitchandak - I also watched your excellent video lesson on custom period. 

[monojchakrab]

@amitchandak Amit - can we modify this code to also include same week from last month?

[amitchandak · Accepted solution]

@monojchakrab , Create week or month columns

min week start of month = minx(filter('Date',[Month Year] =earlier([Month Year])),[Week Start date])

week of month = datediff([min week start of month],[date],Week)+1
or
week of month = quotient(datediff([min week start of month],[date],day),7)+1

or

Start Month = eomonth([date],-1)+1

Month Week Start = [Start Month ] + QUOTIENT(datediff([Start Month],[date],day),7) *7

Create month rank

Month Rank = RANKX(all('Date'),'Date'[Month Start],,ASC,Dense)

MWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [week of month] <=max([week of month])))
LMETD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [week of month] <=max([week of month])))

refer

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[monojchakrab]

Thanks @amitchandak .

I will try this out and let you know