calculate value for previous week of the month

[lawada]

hi everyone

im trying to find week over week change for weeks of the month 

ex. if we are now in the month of FEB2022, weeks would be:

W1: 1-2 till 5-2

W2: 6-2 till 12-2

W3:13-2 till 19-2

W4: 20-2 till 26-2

W5: 27-2 till 28-2

i want to calculate the week over week change and for that i need to find the value for previous week

example with data:

W1: 2478

W2: 3457

W3:3359

W4:3456

W5:574 

week over week change for W2 would be (3457-2478)/2478=39.5

[v-zhangti · Accepted solution]

Hi, @lawada 

You can try the following methods.

Table:

DATE = CALENDAR(DATE(2022,2,1),TODAY())

Column:

Weekday = WEEKDAY([Date],1)

Week No = 
Var minweekday=CALCULATE(MIN('DATE'[Weekday]),FILTER('DATE',[Date]=MIN('DATE'[Date])))
Var week=CALCULATE(COUNT('DATE'[Weekday]),FILTER('DATE',[Date]<=EARLIER('DATE'[Date])&&[Weekday]=1))
Return
IF(minweekday<>1,week+1,week)

Week = "W"&[Week No]

Measure:

Current week value = SUM('Table'[Value])

Previous week value = 
CALCULATE(SUM('Table'[Value]),FILTER(ALL('DATE'),[Week No]=SELECTEDVALUE('DATE'[Week No])-1))

Change = DIVIDE([Current week value]-[Previous week value],[Previous week value])

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hello:

Having a date table with week no will help. Please see attached file with answer. As you get into future years a minor tweak to the measure will take that into account. Hope this helps. Please see link below.

https://drive.google.com/file/d/1IWRpNWJdj-DM22-91V068oSkQt8PQK5-/view?usp=sharing

[AlexisOlson]

A week column in the date table can help as long as it matches what @lawada is trying to do.

Rather than WEEKNUM ( Dates[Date] ), something more like ROUNDUP ( DAY ( Dates[Date] ) / 7, 0 ) might do the trick.