cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## previous week

Help please calculate the difference in company values ​​for each week.

There is a table with 4 columns

Week       Company         Value  MyRank.1

 33 Company A 44 4 33 Company B 44 3 33 Company D 50 2 33 Company C 100 1 34 Company B 20 4 34 Company A 60 3 34 Company C 80 2 34 Company D 200 1 35 Company B 1000 4 35 Company A 1123 3 35 Company C 2000 2 35 Company D 2000 1

It is necessary to calculate the value of one week in comparison with the previous one.

The result should look like this:

 Week Company Value MyRank.1 (Value (week))-(value(week-1)) 33 Company A 44 4 0 33 Company B 44 3 0 33 Company D 50 2 0 33 Company C 100 1 0 34 Company B 20 4 -24 34 Company A 60 3 16 34 Company C 80 2 -20 34 Company D 200 1 150 35 Company B 1000 4 980 35 Company A 1123 3 1063 35 Company C 2000 2 1920 35 Company D 2000 1 1800

or like this:

 Week Company Value MyRank.1 (value(week-1)) 33 Company A 44 4 0 33 Company B 44 3 0 33 Company D 50 2 0 33 Company C 100 1 0 34 Company B 20 4 20 34 Company A 60 3 44 34 Company C 80 2 100 34 Company D 200 1 50 35 Company B 1000 4 20 35 Company A 1123 3 60 35 Company C 2000 2 80 35 Company D 2000 1 200

I tried to solve the problem in two ways. First through the Dax function LOOKUPVALUE.

The second way is to add a custom column with the value of the week to one less than:

PrevWeekss =
SUMX( FILTER(
'rank';
'rank'[Week] = EARLIER('rank'[PreWeek])
)
;'rank'[Value]
)+0

But resalt incorrect:

 Week Company Value MyRank.1 PreWeeks 33 Company A 44 4 0 33 Company B 44 3 0 33 Company C 100 1 0 33 Company D 50 2 0 34 Company A 60 3 238 34 Company B 20 4 238 34 Company C 80 2 238 34 Company D 200 1 238 35 Company A 1123 3 360 35 Company B 1000 4 360 35 Company C 2000 2 360 35 Company D 2000 1 360

pbix on OneDrive

I will be glad to any help and ideas=)

1 ACCEPTED SOLUTION
Super User

Hey,

not sure, but to me thiscalculated column looks like the first solution you were looking for

```Column =
var prevWeek = LOOKUPVALUE(Table1[Value], 'Table1'[Company],'Table1'[Company],Table1[Week],'Table1'[Week]-1)
return
IF(ISBLANK(prevWeek),0,
'Table1'[Value] -
LOOKUPVALUE(Table1[Value], 'Table1'[Company],'Table1'[Company],Table1[Week],'Table1'[Week]-1))```

a screen shot from your sample file

Hope this helps

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
8 REPLIES 8
Super User

Hey,

not sure, but to me thiscalculated column looks like the first solution you were looking for

```Column =
var prevWeek = LOOKUPVALUE(Table1[Value], 'Table1'[Company],'Table1'[Company],Table1[Week],'Table1'[Week]-1)
return
IF(ISBLANK(prevWeek),0,
'Table1'[Value] -
LOOKUPVALUE(Table1[Value], 'Table1'[Company],'Table1'[Company],Table1[Week],'Table1'[Week]-1))```

a screen shot from your sample file

Hope this helps

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

What if I have 33,34,37,38 weeks and want difference between previous week? Table1[week]-1 will not work as I don't have data for 35 , 36 weeks.
for example it looks like this
week  Value difference
33       100    NA
34       200   -100
37       400   -200
38       350   150

Super User

Hey,
this DAX statement creates a calculated column that determines the value of the previous week.

```prevweekvalue =
var currentweek = 'Table1'[week]
var prevweek =
CALCULATE(
MAX('Table1'[week])
,FILTER(ALL('Table1')
,'Table1'[week] < currentweek
)
)
return
LOOKUPVALUE('Table1'[value],Table1[week],prevweek)```

Regards

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Sure , this solution doesn't fullfill the cause when I try to extract previous week for 37 it gives 36 instead of 34.
35 and 36 weeks are not availble so when I go for previous week of 37 it should give 34 as previous week.

Super User

Hey, not sure what you are saying, but this looks pretty much reasonable to me:

Regards

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

what if I have 33,34,38,39 weeks and difference of Table1[value]?

Super User

Hey @Anonymous,

Then the DAX would be a little more complex

1. determine the current weeknumber
2. determine the max weeknumber smaller than the current weeknumber
3. use both weeknumbers to make the appropriate calculation

I recommend that you start a new question, and please provide sample data.

Regards

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Works perfectly! Thank you! Apparently my mistake was that I was trying to do it with the help of a measure

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors