Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Creating a new value based on previous row

‎10-12-2018 11:14 AM

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?

Labels:
Message 1 of 7
1,860 Views
0
1 ACCEPTED SOLUTION
Anonymous
Not applicable

‎10-14-2018 08:07 PM

@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())

1.PNG

Regards,
Lydia

View solution in original post

Message 2 of 7
1,841 Views
0
6 REPLIES 6
Anonymous
Not applicable

‎10-12-2018 10:11 AM

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?

Message 7 of 7
1,846 Views
0
Anonymous
Not applicable

‎10-12-2018 11:04 AM

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?

Message 6 of 7
1,834 Views
0
Anonymous
Not applicable

‎10-14-2018 08:07 PM

@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())

1.PNG

Regards,
Lydia

Message 2 of 7
1,842 Views
0
Anonymous
Not applicable
In response to Anonymous

‎10-18-2018 06:21 AM

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

Message 4 of 7
1,814 Views
0
Anonymous
Not applicable
In response to Anonymous

‎10-23-2018 01:46 AM

@Anonymous,

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

Regards,
Lydia

Message 5 of 7
1,797 Views
0
Anonymous
Not applicable
In response to Anonymous

‎10-16-2018 06:12 PM

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.

Message 3 of 7
1,821 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All