M Code for where date Between Date 1 and Date 2

[DebbieE]

I have a date table with the following columns

Date,               LatestReportDate, 5YearsPreLatestreportdate

01/01/2014    01/01/2019             01/01/2014

02/01/2014    01/01/2019              01/01/2014

03/01/2014    01/01/2019               01/01/2014

I want to create a conditional Column, where Date Between LatestreportDate and 5yearsLatestReportDate Then True

Else False

But Im struggling with the syntax

If anyone could pop me across the proper syntax for M. That would be great

[DebbieE]

Thanks so much. Worked a treat

[AlB · Accepted solution]

Hello @DebbieE

Place the following M code in a blank query to see the steps. The last step is the important one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDWNzDUNzIwNFbSUQKyIBxLZI6JUqxONDKXgEIjYhTGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, LatestReportDate = _t, #"5YearsPreLatestreportdate" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"LatestReportDate", type date}, {"5YearsPreLatestreportdate", type date}}),

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] <= [LatestReportDate] and [Date] >= [5YearsPreLatestreportdate] then true else false)
in
    #"Added Custom"

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you