previous week

[Anthony007]

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=)

[TomMartens · Accepted 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

[Anonymous]

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

[TomMartens]

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

[Anonymous]

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.

[TomMartens]

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

Regards

Tom

[Anonymous]

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

[TomMartens]

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

[Anthony007]

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