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.
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.
Solved! Go to Solution.
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
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 !
Thank you for spending time on this.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |