Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
I will be glad to any help and ideas=)
Solved! Go to Solution.
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
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
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
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:
Regards
Tom
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
I recommend that you start a new question, and please provide sample data.
Regards
Tom
Works perfectly! Thank you! Apparently my mistake was that I was trying to do it with the help of a measure
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
84 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
65 | |
55 |