Created Date is within Current Week, Past Two Weeks, etc.

[ChelseaLearned]

Hi Everyone

Thanks for reading!  I'm trying to create an open ticket tracker that shows tickets as green, yellow, orange, and red depending on if they're just open in the current week, past two weeks, past month, or older than a month.  I think I've got everything except those bins.  I figure the easiest way is to make a custom column that bins them based on date functions, but it doesn't seem to be working.  I'm new to Power BI, though, so maybe I've missed something.

Here is my column query:

=if [Created Time] is null then null else if Date.IsInCurrentWeek([Created Time]) then "Current Week" else if Date.IsInPreviousNWeeks([Created Time], 2) then "Past Two Weeks" else if Date.IsInCurrentMonth([Created Time]) then "Past Month" else "Older Than a Month"

Everything is returning as "Older Than a Month."  Any ideas?

My [Created Time] looks like this: 1/11/2023 10:39:00 AM

The date queries are supposed to work on datetime queries, so I didn't think that would be a problem.

Thanks!

[ChelseaLearned · Accepted solution]

Thanks for your help!  I found out what I initially did actually worked, just the preview wasn't loading all data.  I had to click "Load more" when selecting the dropdown for the calculated column.

[amitchandak]

@ChelseaLearned , Try this in a new column. Trying to use date, in the place of date time

let
_create = DateTime.Date([Created Time]),
_result = if _create is null then null else if Date.IsInCurrentWeek(_create) then "Current Week" else if Date.IsInPreviousNWeeks(_create, 2) then "Past Two Weeks" else if Date.IsInCurrentMonth(_create) then "Past Month" else "Older Than a Month"

in
_result