Forum Discussion
Show Word if between Two Dates
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.
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
2 Replies
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
Thank you so much, I greatly appreciate it.
