Forum Discussion
Resolver IIIweek on week comparison
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 , 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)+1or
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
5 Replies
- amitchandak
Super User
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)+1or
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
monojchakrabThanks amitchandak .
I will try this out and let you know
- monojchakrab
amitchandak Amit - can we modify this code to also include same week from last month?
- monojchakrab
amitchandak - I also watched your excellent video lesson on custom period.
- 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