cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Glenda
Helper I
Helper I

Using Greater than in a IF foumla

I am trying to set up a new column where 2 things must be met. One is the state and the other is depending on date. The standard formula i use in excel is not working. 

 

Policy = IF (AND('XOX Report'[STATE]=2,'XOX Report'[Date Of Injury]< 1/1/2000) ,"POL 1" , "POL 2")

 

What am i doing wrong?

 

1 ACCEPTED SOLUTION
ManNVSM
Resolver II
Resolver II

Dear @Glenda

    Try this:

Policy = IF( and(  value( W_DAY_D[Year]) = 2017,  value(FORMAT( W_DAY_D[Date], "YYYYMMDD")) < 20170901 ) ,"my True", "my False")

 

My result

If_Date.png

View solution in original post

5 REPLIES 5
ManNVSM
Resolver II
Resolver II

Dear @Glenda

    Try this:

Policy = IF( and(  value( W_DAY_D[Year]) = 2017,  value(FORMAT( W_DAY_D[Date], "YYYYMMDD")) < 20170901 ) ,"my True", "my False")

 

My result

If_Date.png

MarcelBeug
Community Champion
Community Champion

< is less than, use > instead

 

Otherwise your formula works fine with me.

 

By the way please don't use "is not working": either provide the error message, or explain the actual result and the expected result.

Specializing in Power Query Formula Language (M)

If I use less than I get the faluse result and if I use greater than I get the ture result againt all records.

 

Thanks for the tip (first time posting)

 

@GlendaYou are welcome.

 

Can you post your script here (your last comment).

Maybe I'll help your case and your expectation.

 

Regards.

Hello all,

 

I am having troubles implementing this formala in one of my BI:

 

Badge Completion Status = if('Badges table'[Badges]="Bronze",IF([Badge count]>=3,"Complete","Not Complete"),IF('Badges table'[Badges]="Silver",IF([Badge count]>=2,"Complete","Not Complete"),IF('Badges table'[Badges]="Gold",IF([Badge count]>=4,"Complete","Not Complete"))))

 

It seems to work perfectly in Excel, but not in BI.

Short explanation: i want to show completion status based on number of badges received. If greater or equal than x, then show completed. Can you please help me? See below screen shot which shows incorrect results...

thanks in advance

Kevin

 

screen shot.PNG

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors