Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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" ),
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |