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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
98 | |
96 | |
87 | |
68 |
User | Count |
---|---|
167 | |
133 | |
130 | |
103 | |
96 |