How convert Excel formula with relative references to DAX

[Anonymous]

Below I have a EXCEL function with multiple relative referenes to data in the next row. How can I convert this to DAX?

So the goal of the function is to calculate the latest shipment date before the product has to go to the next step.

If the product from row 1 is in one of the 6 steps (AA, AB, AC, AD, BB, BC) Then check the next row in first column if it the same product. Then if it's still same product, then check if ColumnD and ColumnQ have same value. If have same value then return the date in ColumnL from next row. 

If columnD and ColumnQ doesn't have same value, then return 0

I hope I explained it correctly. 

IF(
    OR(
        Data[AP] = "AA",
        Data[AP] = "AB",
        Data[AP] = "AC",
        Data[AP] = "AD",
        Data[AP] = "BB",
        Data[AP] = "BC"
    ),
     IF(
        Data[@Colum1] = A3,  ## a3 is relative reference to the next row compared to the row that is being calculated in this function to check if the order nr in column1 row1 is same as "cell A3" column1 row2 etc etc.
         IF(
            Data[@[ColumnQ]] = Data[@ColumnD],
            L3 - 0.9, ## L3 is relative reference to the date of next row in columnL 
            0
        ),
         0
    ),
     0
)

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you want to convert the formula of excel, but for your needs, I don't understand Data[AP] very well, is this a column?

Can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. This will make this problem more clearly.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Product		Step		Start date		AP		Plan date		Shipdate		Step order		Date when needed
5418 / 2		20		07-02-23		AA		07-02-23		07-02-23		20		07-02-23
5418 / 2		25		08-02-23		BB		08-02-23		07-02-23		20		00-01-00
5418 / 2		30		08-02-23		BC		08-02-23		07-02-23		20		00-01-00
5418 / 2		35		09-02-23		BB		09-02-23		07-02-23		20		00-01-00
5418 / 2		40		10-02-23		BD		10-02-23		07-02-23		20		00-01-00
5567 / 1		30		10-02-23		AB		10-02-23		10-02-23		30		07-03-23
5567 / 1		35		08-03-23		BB		08-03-23		10-02-23		30		00-01-00
5567 / 1		40		09-03-23		BE		09-03-23		10-02-23		30		00-01-00
5567 / 1		50		09-03-23		BF		09-03-23		10-02-23		30		00-01-00
5567 / 1		60		10-03-23		BG		10-03-23		10-02-23		30		00-01-00
5567 / 1		65		14-03-23		BB		14-03-23		10-02-23		30		00-01-00
5567 / 1		70		15-03-23		BD		15-03-23		10-02-23		30		00-01-00
5570 / 1		65		28-02-23		BB		28-02-23		13-02-23		70		00-01-00
5570 / 1		70		28-02-23		AB		28-02-23		13-02-23		70		13-03-23
5570 / 1		75		14-03-23		BB		14-03-23		13-02-23		70		00-01-00
5570 / 1		80		15-03-23		BF		15-03-23		13-02-23		70		00-01-00
8193 / 1		140		09-02-23		AB		09-02-23		09-02-23		140		21-02-23
8193 / 1		145		22-02-23		BB		22-02-23		09-02-23		140		00-01-00
8193 / 1		150		23-02-23		BH		23-02-23		09-02-23		140		00-01-00
8193 / 1		155		24-02-23		BB		24-02-23		09-02-23		140		00-01-00
8193 / 1		160		24-02-23		AB		24-02-23		09-02-23		140		00-01-00
8193 / 1		165		10-03-23		BB		10-03-23		09-02-23		140		00-01-00
8193 / 1		170		13-03-23		BH		13-03-23		09-02-23		140		00-01-00
8193 / 1		175		14-03-23		BE		14-03-23		09-02-23		140		00-01-00
8193 / 1		180		15-03-23		BF		15-03-23		09-02-23		140		00-01-00
8233 / 1		160		15-02-23		AB		15-02-23		15-02-23		160		23-02-23
8233 / 1		165		24-02-23		BB		24-02-23		15-02-23		160		00-01-00
8233 / 1		170		27-02-23		BH		27-02-23		15-02-23		160		00-01-00
8233 / 1		175		28-02-23		BB		28-02-23		15-02-23		160		00-01-00
8233 / 1		180		01-03-23		BR		01-03-23		15-02-23		160		00-01-00
8428 / 2		20		07-02-23		AA		07-02-23		07-02-23		20		00-01-00

So in the Table the following formula is used to calculate the "date when needed".  

=IFERROR(
 IF(
    OR(
              [@AP] = "AA",
              [@AP] = "AB",
              [@AP] = "AC"
    ),
     IF(
              [@Product] = A3,  ## a3 is relative reference to the next row compared to the row that is being calculated in this function to check if the order nr in column1 row1 is same as "cell A3" column1 row2 etc etc.
         IF(
                 [@[Step order]] =  [@Step],
            E3 - 0.9, ## E3 is relative reference to the date of next row in columnE "Plandate" 
            0
        ),
         0
    ),
     0
)

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

Thanks for your quick response!

Here are the steps you can refer to :
(1)Due to check the row or the next row, we need to add a index column in Power Query Editor.

(2)Then we apply the data to Desktop and we can click "New Column" and enter this:

Date when needed = var _ap=[AP]
var _cur_row =[Index]
var _next_row =  FILTER( 'Table','Table'[Index]=_cur_row+1 && 'Table'[Product]=EARLIER('Table'[Product]))

return
IF(_ap in {"AA","AB","AC"} && COUNTROWS(_next_row)>=1 && [Step]= [Step order], MAXX(_next_row,[Plan date])-1,BLANK())

Then we can meet your need , the result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Wow that already looks really good. and the solution sounds really solid. However, I forgot to ask. Is this possible in direct query? I'm setting up a dashboard for my company in direct query mode, I noticed that adding index column isn't possible in direct query. Would it be possible to reference to row number instead of index number?

[v-yueyunzh-msft]

Hi, @Anonymous 

That's right, add an index column if it's your direct query mode
It really doesn't work. But you can try replacing my index column with something else. However, you must ensure that there is a column in your data source that can be used to determine which row is currently (like my index column).

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Can I use the product row as "index" row? Or what if I add an index column in the sql query with row_nr or rank? 

I also noticed that ORDER BY is also disabled in direct query, so maybe I could use RANK product ID. then all the product id that match will get same rank, right? So then we could use the matching rank nr to determine if we need to check the row or not. would this work?

[v-yueyunzh-msft]

Hi , @Anonymous 

As I understand it, the best thing to do is to include an index column at your data source. For your [Product] column, this column has duplicate values, so we can't tell which rows this is. If we want to create a row number in Dax, we generally use a column that has no duplicate values and can judge the size. And based on the data you provided, I didn't find a unique column to judge the current row in which rows, if the same value exists with rankx, then it will affect the final result.

For more information, you can refer to :
Generating Row Number in Power BI Visualization Using DAX - RADACAD

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly