Forum Discussion
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 |
I will be glad to any help and ideas=)
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
8 Replies
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
Works perfectly! Thank you! Apparently my mistake was that I was trying to do it with the help of a measure
Hey Anonymous,
Then the DAX would be a little more complex- determine the current weeknumber
- determine the max weeknumber smaller than the current weeknumber
- use both weeknumbers to make the appropriate calculation
I recommend that you start a new question, and please provide sample data.
Regards
Tom
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
