Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anthony007
Helper I
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

33Company A444
33Company B443
33Company D502
33Company C1001
34Company B204
34Company A603
34Company C802
34Company D2001
35Company B10004
35Company A11233
35Company C20002
35Company D20001

 

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

The result should look like this:

WeekCompanyValueMyRank.1(Value (week))-(value(week-1))
33Company A4440
33Company B4430
33Company D5020
33Company C10010
34Company B204-24
34Company A60316
34Company C802-20
34Company D2001150
35Company B10004980
35Company A112331063
35Company C200021920
35Company D200011800

 

or like this:

WeekCompanyValueMyRank.1(value(week-1))
33Company A4440
33Company B4430
33Company D5020
33Company C10010
34Company B20420
34Company A60344
34Company C802100
34Company D200150
35Company B1000420
35Company A1123360
35Company C2000280
35Company D20001200

 

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:

WeekCompanyValueMyRank.1PreWeeks
33Company A4440
33Company B4430
33Company C10010
33Company D5020
34Company A603238
34Company B204238
34Company C802238
34Company D2001238
35Company A11233360
35Company B10004360
35Company C20002360
35Company D20001360

 

pbix on OneDrive

 

I will be glad to any help and ideas=)

1 ACCEPTED SOLUTION
TomMartens
Super User
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

Prev Week calculation.png

 

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

View solution in original post

8 REPLIES 8
TomMartens
Super User
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

Prev Week calculation.png

 

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

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)

 

If you have any further questions, please start a new thread,, because the question is already answered.

 

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.

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

image.png

 

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]?

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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