cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prithivi97
Frequent Visitor

Previous row with IF formula in Power BI

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

 

prithivi97_0-1635174722118.png

: =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: 

 

prithivi97_1-1635174782626.png

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

 

3 REPLIES 3
v-kalyj-msft
Community Support
Community Support

Hi @prithivi97 ,

According to your description, heres my solution.

vkalyjmsft_0-1635820249343.png

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
Super User
Super User

@prithivi97  provide sample data and desired output.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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 DEFG
 Concat> Today? FDR?Position ID
33/4/2019NoNot relevant50000457
430/4/2019NoNot relevant50000457
520/6/2019NoNot relevant50000457
61/9/2019NoNot relevant50000457
724/10/2019NoNot relevant50000457
87/11/2019NoNot relevant50000457
926/3/2021NoNot relevant50000457
101/4/2021NoNot relevant50000457
1113/9/2021NoNot relevant50000457
121/10/2021NoCurrent50000457

 

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"))

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!