Forum Discussion
date category in query editor
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.
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
4 Replies
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.
NathanielFor 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