topic calculate value for previous week of the month in DAX Commands and Tips

calculate value for previous week of the month

lawada — Mon, 28 Feb 2022 12:52:28 GMT

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

Re: calculate value for previous week of the month

Whitewater100 — Mon, 28 Feb 2022 13:59:28 GMT

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

Re: calculate value for previous week of the month

AlexisOlson — Mon, 28 Feb 2022 21:42:28 GMT

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.

Re: calculate value for previous week of the month

v-zhangti — Thu, 03 Mar 2022 06:29:13 GMT

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.