cancel
Showing results for
Did you mean:
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
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``````

Best 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.
5 REPLIES 5
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``````

Best 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.
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!

@mahoneypa HoosierBI on YouTube

Frequent Visitor

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

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"

Frequent Visitor

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

Announcements

#### Welcome to the User Group Public Preview

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

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors