Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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" ),
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)
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!
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" ),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |