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 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?

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

6 Replies

  • 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?

  • 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?

  • 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous

       

      Follow up to this.  What changes need to be made to reset the Row/Column counters when a second Week 0 Meeting occurs for the same Join Col value?  I've been trying a few things, but my limited knowledge of PBI & DAX are hindering my efforts.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Anonymous,

        Please open a new thread with sample data and expected result.

        Regards,
        Lydia