Conditional Column - PowerQuery - Detect if date is in the last 7 or 30 days

[DavidGorgan]

Hello,

I have a table that contains a date column ( [time_recorded]).

I'm currently trying to create a conditional column that will display the following values ("Past week", "Past Month", "Other").

I've tried the following formula:

= Table.AddColumn(#"Expanded bi sites", "period", each if  Date.IsInPreviousNDays([time_record], 7) = true then "Past 7 Days" else if Date.IsInPreviousNDays([time_record], 30) = true then "Past 30 Days" else "Other Period")

Please can someone advise?

[amitchandak]

@DavidGorgan , check if this can help

Table.AddColumn(#"Expanded bi sites", "period", each if Date.Date.IsInPreviousWeek([time_record]) = true then "Past week" else if Date.Date.IsInPreviousMonth([time_record]) = true then "Past Month" else "Other")

[DavidGorgan]

Hello @amitchandak,

Thank you for getting back to me.

I've tried the formula and I get the following error: 

"Expression.Error: The name 'Date.Date.IsInPreviousWeek' wasn't recognized. Make sure it's spelled correctly."

[amitchandak]

@DavidGorgan , My mistake, try like 

Table.AddColumn(#"Expanded bi sites", "period", each if Date.IsInPreviousWeek([time_record]) = true then "Past week" else if Date.IsInPreviousMonth([time_record]) = true then "Past Month" else "Other")

[DavidGorgan]

Hello @amitchandak,

Thank you for your reply again.

This formula goes through successfully but it still does not work - rows display "error".