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.
Hello,
I would like to create a custom column into query editor which give the date category as follow
if date1 is null then 'No Data'
if date1 - today() < 0 then 'Date Past'
if date1 - today() <=30 then '0 - 30 Days'
else '+ 30 Day'.
The if function work but it does not recognized the value of today()
Does someone know how to do this calculation
Thanks in advance for your help.
Solved! Go to Solution.
Hi @Anonymous ,
You need to use
DateTime.Date(DateTime.LocalNow()))
You may want to go to this posting, I included my pbix at the end
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @Anonymous ,
You need to use
DateTime.Date(DateTime.LocalNow()))
You may want to go to this posting, I included my pbix at the end
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
For the community, Here's the final code:
if [Date1] is null then "No Data" else
if (Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) < 0 then "Date Past"
else if (Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) >= 0 and
(Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) <= 30
then "0 - 30 Days"
else if (Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) > 30 and
(Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) <= 60
then "31 - 60 Days"
else if (Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) > 60 and
(Duration.Days(DateTime.Date([Date1])-DateTime.Date(DateTime.LocalNow()))) <= 90
then "61 - 90 Days" else "+ 90 Days"
Hi @Anonymous ,
Good job! You might also look into using SWITCH() in these situations with variables to make debug easier.
Nathaniel
Proud to be a Super User!
Thank you! In Query Editor, if you are adding a custom column, does the switch function will works?
If you could provide me a simple example with a custom column in Query editor, I will be glad to test it.
Best Regards,
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 |
---|---|
105 | |
104 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |