Forum Discussion
Query Editor Custom Column If function on Date
Hi, I have to create a custom column in the query editor which will show an indicator for the number of days, based on date.
If using calculated columns the formula will show as below...
Last Two Months = IF(TODAY()-[Date]<=30,1,IF(TODAY()-[Date]<=60,2,0))
How can I get the same result when creating a custom column in the query editor?
Thank you in advance.
In general, your formula will be:
= if Duration.Days(DateTime.LocalNow() - [Date]) <= 30 then 1 else if Duration.Days(DateTime.LocalNow() - [Date]) <= 60 then 2 else 0
Couple of things. First, if you have dates in the future, your days between will come out negative (which is <=30) so you might wish to account for that.
Second, this assumes a DateTime column type for [Date]. If you do not have a DateTime column type for Date, you can add a step to transform that column to DateTime before you add this custom column. Otherwise, assuming that you have a Date or Text column, your formula is:
= if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) <= 30 then 1 else if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) <= 60 then 2 else 0
2 Replies
In general, your formula will be:
= if Duration.Days(DateTime.LocalNow() - [Date]) <= 30 then 1 else if Duration.Days(DateTime.LocalNow() - [Date]) <= 60 then 2 else 0
Couple of things. First, if you have dates in the future, your days between will come out negative (which is <=30) so you might wish to account for that.
Second, this assumes a DateTime column type for [Date]. If you do not have a DateTime column type for Date, you can add a step to transform that column to DateTime before you add this custom column. Otherwise, assuming that you have a Date or Text column, your formula is:
= if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) <= 30 then 1 else if Duration.Days(DateTime.LocalNow() - DateTime.From([Date])) <= 60 then 2 else 0
Thank you very much! This solved my problem ! :smileyvery-happy:
Thank you for spending time on this.
