Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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(...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

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

Nathaniel

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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,