Show Word if between Two Dates

[ElliotP]

Evening,

I'm trying to create a column for dates which will show different categorisations depending upon the date range. The date ranges aren't within my table and I'm trying to code them into the M/column.

When I attempted to create the column in the report view with the code it returns "returns multiple values":

Tester2021 = IF(DATESBETWEEN('itemdetailsdogfood$'[Date], DATE(2017,1,1), DATE(2017,7,15)),"Term Time", "Normal")

When I create the custom column in the query editor, I'm not sure of the syntax for M:

if([Date] > DATE(2017,1,1) < DATE(2017,7,15), "Term Time", "Normal")

Thoughts or suggestions? I've searched the forums and the internet to no avail. I've had a look at the M reference, but that seems to be focused upon power query as opposed to the M used in Custom Columns.

[MFelix · Accepted solution]

Hi @ElliotP,

Add a custom column with the following code:

= 
 if 
 [Date] >=  #date(2017, 1, 1)  and [Date]  <=   #date(2017, 7, 15) 
then "Term Time"

else "Normal"

The M code is:

= Table.AddColumn(#"Renamed Columns", "Custom.1", each if 
 [Date] >=  #date(2017, 1, 1) 
and [Date]  <=   #date(2017, 7, 15) 
then "Term Time"

else "Normal")

Regards,

MFelix

[ElliotP]

Thank you so much, I greatly appreciate it.