Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shelley
Continued Contributor
Continued Contributor

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

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!

1 ACCEPTED SOLUTION
Shelley
Continued Contributor
Continued Contributor

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" ),

View solution in original post

3 REPLIES 3
DAX0110
Resolver V
Resolver V

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
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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" ),

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.