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

Power query - if function with dates

I have table with two columns with dates Release date and closed date.

I want to create custom column, but I don't know how i should create formula. 

Basically I need 3 category

1) if relase date is before 1.1.2020 then "1)

2) if release date is after 31/12/2019 and before 1/1/2020 then "2"

3) if closed date is before 1/1/2021 then "3"

How formula should look like?

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @PrzeJul ,

You can add custom column with the formula as shown in below screenshot, later please adjust the conditions base on your scenario..

=if [Release date] < #date(2020, 1, 1) then  1
      else if [Release date] > #date(2019, 12, 31) and [Release date] < #date(2021, 1, 1) then 2
      else if [closed date] < #date(2021, 1, 1) then 3
      else  0

yingyinr_0-1617862608195.pngBest Regards

 

 

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

View solution in original post

5 REPLIES 5
yingyinr
Community Support
Community Support

Hi @PrzeJul ,

You can add custom column with the formula as shown in below screenshot, later please adjust the conditions base on your scenario..

=if [Release date] < #date(2020, 1, 1) then  1
      else if [Release date] > #date(2019, 12, 31) and [Release date] < #date(2021, 1, 1) then 2
      else if [closed date] < #date(2021, 1, 1) then 3
      else  0

yingyinr_0-1617862608195.pngBest Regards

 

 

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

View solution in original post

mahoneypat
Super User IV
Super User IV

You can use this formula in a custom column in the query editor

 

= if [release date] < #date(2020,1,1) then "1" else if ([release date] > #date(2019,12,31) and [release date] < #date(2020,1,1)) then "2" else if [closed date] < #date(2021,1,1) then "3" else "other"

 

Note your #2 condition seems off, so you can adjust as needed.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I wrote this and I have "Error" in each cell instead of "1", "2" etc. :c

 

Angith_Nair
Resolver II
Resolver II

Hi @PrzeJul ,

Create a custom column in Power Query level and write this code..

Custom Column = if [Release date] < #date(2020, 01, 01) then "1" else if [Release date] > #date( 2019, 12, 31) and  [Release date] < #date( 2020, 01, 01) then "2" else if [closed date] < #date (2021, 01, 01) then "3" else "0"

Hi, I wrote this code and I have errors in each cell. 

PrzeJul_0-1617775782583.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors