date category in query editor

[Anonymous]

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.

[Nathaniel_C · Accepted 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

Using the today function

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

[Anonymous]

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"

[Nathaniel_C]

Hi @Anonymous ,

Good job! You might also look into using SWITCH() in these situations with variables to make debug easier.

Nathaniel

[Anonymous]

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,