Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Anonymous ,
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
Hi @Anonymous ,
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I wrote this and I have "Error" in each cell instead of "1", "2" etc. :c
I encountered the same issue. Check if the Data Type of your date columns ([release date], [closed date]) is in "Date". The above doesn't seem to work on "Date/Time" data. I tried it on "Date" data and it worked.
Hi @Anonymous ,
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.
I encountered the same issue. Check if the Data Type of your date columns ([release date], [closed date]) is in "Date". The above doesn't seem to work on "Date/Time" data. I tried it on "Date" data and it worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |