How to limit power query to last 12 months of data based on Date field

[Shelley]

We have a data table from SAP for which we only ever want to use the last 12 months of data in Power BI. I was trying to create a custom flag column, with which I could then filter it to be = 1. I am trying the following formula:

= Table.AddColumn(dbo_ServiceOrder, "Flag in Data Range", each if [Create_Date] > Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-12) then 1 else 0)

This returns no syntax errors, but when I look at my table, there is an error:

Expression.Error: We cannot apply operator < to types Date and DateTime.
Details:
    Operator=<
    Left=12/1/2016
    Right=7/27/2016 12:00:00 AM

Can anyone out there, please help? Thanks!

[DAX0110]

Hi @Shelley, you just have to make the two sides' data type match up "exactly" (the M language is very picky): maybe like this: (removed DateTime.Date)

= Table.AddColumn(dbo_ServiceOrder, "Flag in Data Range", each if [Create_Date] > Date.AddMonths(DateTime.LocalNow(),-12) then 1 else 0)

[Shelley]

Thanks for the idea. When I tried it, the refresh ran really, really slow, so I cancelled it and began trying other functions. I think I finally got it to work with this:

#"Added Conditional Column" = Table.AddColumn(dbo_ServiceOrder, "Flag in Data Range", each if Date.IsInPreviousNMonths(Date.AddMonths([Create_Date], 0), 12) then "1" else "0" ),

This checks to see if the [Create_Date] field for the record is in the Previous 12 months from Today and if so, "1" otherwise "0" - once I ensured it was working properly, I was able to filter the column on "1"

Thanks again!

[Shelley · Accepted solution]

The above works, but it is literal in that it is looking for previous N months. Therefore, if today is December, it will flag only dates in November and earlier for 12 months. I want data through yesterday, for the last year (or 365 days), so then I did this instead and it works for me:

#"Added Conditional Column" = Table.AddColumn(dbo_ServiceOrder, "Custom", each if Date.IsInPreviousNDays(Date.AddMonths([Create_Date], 0), 365) then "1" else "0" ),