Query editor: How to sort a list of dates into either before/after 7 days in the past

[Rearles]

Hi everyone,

Due to confidentiality reasons unfortunately I cannot share any of the actual data I am working with, but I will try to explain as best as I can.

Basically, I have a large dataset in Power BI where one of the columns is titled 'Next Inspection Date' and shows a series of dates ranging from ones in the past to ones in the future. The dates refer to when inspections of certain properties should have taken place or will take place. 

I would like to create a new custom column in Query editor that shows how these listed dates compare to a reference point of 7 days in the past, so the column would either read 'Overdue' if the inspection date listed is 7 days or more in the past, or 'Ok' if it is within 7 days of the past or in the future. 

I have currently tried to use an if statement to implement this which reads:

But there seems to be errors.

So my 3 questions are:

1) Is there an issue with the '<=' sign i am using? If so, is there another way to say 'is earlier than or at same time of' instead of '<='?

2) Are there any errors with the functions I'm using? (e.g. Date.AddDays(DateTimeLocalNow, -7) being used to say 7 days before the present time)

3) Is there a completely better way to achieve my purpose than using if statements? I think I would prefer to stay using an if statement if possible as I think I understand them fairly well, but if using an if statement altogether is wrong then please let me know!

Thanks very much if you've read to this point! Best wishes, Rob

[jennratten · Accepted solution]

Hello Rob!  This will do the trick...

First add a custom column named Days Elapsed.  You can click Add Custom Column and then paste this into the editor:

Number.From(Date.From(DateTime.FixedLocalNow()))- Number.From([Next Inspection Date])

Explanation: Number.From is converting the date into a number and comparing it the numeric version of today's date.

After you add this column you need to change the type to a number.  You can either click on the column and change the type (to whole number) using the menu buttons, or you can look in the formula bar and add this little bit of text to the end.  See the text in the red box below.  This prevents adding another step to the query just to set the type.

Then Add New Column > Overdue Status > paste this into the editor:

if [Days Elapsed] > 7 then "Overdue" else "Ok"

This time change the type to text (in the formula bar).

You don't have to create a separate column for the Days Elapsed, however, if you do, you will be able to see the overdue status and also how many days each is overdue by.

Here are the two lines of complete script.

    AddDaysElapsed = Table.AddColumn(SourceTable, "Days Elapsed", each Number.From(Date.From(DateTime.FixedLocalNow()))- Number.From([Date]), Int64.Type),
    AddOverdueStatus = Table.AddColumn(AddDaysElapsed, "Overdue Status", each if [Days Elapsed] > 7 then "Overdue" else "Okay", type text)

[AlexisOlson · Accepted solution]

You're really close!

You just need to write DateTime.LocalNow() with the parentheses.

[Rearles]

Hi Alexis,

Thank you so much! Can't believe it was such a simple fix! That worked a treat! Have a good day 🙂

Best wishes, Rob

[jennratten · Accepted solution]

Hello Rob!  This will do the trick...

First add a custom column named Days Elapsed.  You can click Add Custom Column and then paste this into the editor:

Number.From(Date.From(DateTime.FixedLocalNow()))- Number.From([Next Inspection Date])

Explanation: Number.From is converting the date into a number and comparing it the numeric version of today's date.

After you add this column you need to change the type to a number.  You can either click on the column and change the type (to whole number) using the menu buttons, or you can look in the formula bar and add this little bit of text to the end.  See the text in the red box below.  This prevents adding another step to the query just to set the type.

Then Add New Column > Overdue Status > paste this into the editor:

if [Days Elapsed] > 7 then "Overdue" else "Ok"

This time change the type to text (in the formula bar).

You don't have to create a separate column for the Days Elapsed, however, if you do, you will be able to see the overdue status and also how many days each is overdue by.

Here are the two lines of complete script.

    AddDaysElapsed = Table.AddColumn(SourceTable, "Days Elapsed", each Number.From(Date.From(DateTime.FixedLocalNow()))- Number.From([Date]), Int64.Type),
    AddOverdueStatus = Table.AddColumn(AddDaysElapsed, "Overdue Status", each if [Days Elapsed] > 7 then "Overdue" else "Okay", type text)

[Rearles]

Hi jennratten,

Thank you so much for taking the time to not only fix my error but actually improve the method! Really appreciate it. Having a days elapsed column is actually very helpful. Have a good day 🙂

Best wishes, Rob

[jennratten]

You are very welcome!  You too!