Forum Discussion

Anthony007's avatar
Anthony007
Helper I
8 years ago
Solved

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 ...
Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

TomMartens's avatar
TomMartens
Super User
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    TomMartens
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        TomMartens
        Super User
        8 years ago

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

         

        Regards

        Tom