Previous row with IF formula in Power BI

[Anonymous]

Hi there, I am trying to convert below given excel formula into power bi formula:

: =IF((I4<>I5)*(E4="No"),"Current",IF((I4<>I5)*(E4="Yes"),"FDR","Not relevant"))

I have created the index row starting with 0, 1.. to find the previous value - however, the return value isn't correct in power bi. below is my formula: 

: Column FDR = IF([Position ID] <> [Last Position ID]*([4. Today]="No"), "Current", IF([Position ID] <> [Last Position ID]*([4. Today]="YES"),"FDR", "Not Relevant"))

The return values are only "Current" and "FDR". Although, FDR only should come in YES but it shows for No and Current shows for Yes & No. I am not sure what i am missing in this formula...

Really appreciate your input in this problem..

[v-yanjiang-msft]

Hi @Anonymous ，

According to your description, here’s my solution.

Best Regards,
Community Support Team _ kalyj

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

[smpa01]

@Anonymous  provide sample data and desired output.

[Anonymous]

Hi, thank you for responding..

Position ID is the data set and first three columns are excel formula columns. 

1. Concat - just random date generated in system and i changed the format to (dd/mm/yyyy). 

2. > Today? - =IF(D3*1>TODAY(),"Yes","No")

3. FDR =IF((G3<>G4)*(E3="No"),"Current",IF((G3<>G4)*(E3="Yes"),"FDR","Not relevant"))

             So technically, if position ID G3<>G4 and >Today is No = the return value should be Current.

             If the psoition id G3<>G4 and > Today is Yes = the return value should be FDR (if true) Not                       relevant (if false)

	Column D	E	F	G
	Concat	> Today?	FDR?	Position ID
3	3/4/2019	No	Not relevant	50000457
4	30/4/2019	No	Not relevant	50000457
5	20/6/2019	No	Not relevant	50000457
6	1/9/2019	No	Not relevant	50000457
7	24/10/2019	No	Not relevant	50000457
8	7/11/2019	No	Not relevant	50000457
9	26/3/2021	No	Not relevant	50000457
10	1/4/2021	No	Not relevant	50000457
11	13/9/2021	No	Not relevant	50000457
12	1/10/2021	No	Current	50000457

In power bi - i have created index column and index_1 to get the last position ID (below cell position id). but the return value in power bi is not as expected. Below was the if function measure i used in power bi.

Column FDR = IF([Position ID] <> [Last Position ID]*([4. Today]="No"), "Current", IF([Position ID] <> [Last Position ID]*([4. Today]="YES"),"FDR", "Not Relevant"))