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

I am working on moving a data set from Excel to a live SQL Server connection.  In the Excel version, I have the Calculated value Weeks Out Sales Meeting created using the values in Meeting Week 0 and JoinCol. Here is a sample set of data:

 

 ABCD
 JoinColTransWeekMeeting Week 0Weeks Out Sales Meeting
1002150:WARREN NELSON:NICK AGEE:FRANK MACK1/21/2018  
2002150:WARREN NELSON:NICK AGEE:FRANK MACK1/28/2018  
3002150:WARREN NELSON:NICK AGEE:FRANK MACK2/4/2018  
4002150:WARREN NELSON:NICK AGEE:FRANK MACK2/11/201800
5002150:WARREN NELSON:NICK AGEE:FRANK MACK2/18/2018 1
6002150:WARREN NELSON:NICK AGEE:FRANK MACK2/25/2018 2
7002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/21/2018  
8002150:WARREN NELSON:NICK AGEE:JIM DAVIS1/28/2018  
9002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/4/2018  
10002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/11/201800
11002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/18/2018 1
12002150:WARREN NELSON:NICK AGEE:JIM DAVIS2/25/2018 

2

 


The Column Weeks Out Sales meeting is populated with the following formula in Excel:

=IFERROR(IF(D3=0,0,IF(B3=B2,E2+1,"")),"")

What is the best way to replicate this formula using DAX in PBI?