Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Creating a new value based on previous row

Hello,   Trying to figure this one out.  I have this working in Excel, but want to migrate to using an active SQL Server connection.     In the Excel version, I have the Calculated value Weeks Ou...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous,

    You can create the following columns in your table.

    Rank = RANKX(FILTER(Table1,Table1[JoinCol]=EARLIER(Table1[JoinCol])),Table1[TransWeek],,ASC,Dense)

    Column = CALCULATE(FIRSTNONBLANK(Table1[Rank],1),NOT(ISBLANK(Table1[Meeting Week 0])),ALLEXCEPT(Table1,Table1[JoinCol]))
    Weeks Out Sales Meeting = var tempvalue=Table1[Rank]-Table1[Column] return IF(tempvalue>=0,tempvalue,BLANK())



    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous,

You can create the following columns in your table.

Rank = RANKX(FILTER(Table1,Table1[JoinCol]=EARLIER(Table1[JoinCol])),Table1[TransWeek],,ASC,Dense)

Column = CALCULATE(FIRSTNONBLANK(Table1[Rank],1),NOT(ISBLANK(Table1[Meeting Week 0])),ALLEXCEPT(Table1,Table1[JoinCol]))
Weeks Out Sales Meeting = var tempvalue=Table1[Rank]-Table1[Column] return IF(tempvalue>=0,tempvalue,BLANK())



Regards,
Lydia

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you, Lydia.  That solution works perfectly.

 

The only item I found I had to do was save the workbook before calculating out the last item.