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,38,39 weeks and difference of Table1[value]?

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    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