Forum Discussion
Power Query for a unique date range
Trying to get the current first day of the month minus 1 year and then current first day of the month plus 1 year for a date range using to customize my SQL query for Power BI.
Any help will be greatly appreciated.
Thank you.
Chad
Hi MP-iCONN ,
Here are the steps you can follow：
1. Add Column – Custom Column.
[date] >= #date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(Date.StartOfMonth(DateTime.LocalNow())), 1) and [date] <=#date(Date.Year(DateTime.LocalNow()) +1 , Date.Month(Date.StartOfMonth(DateTime.LocalNow())), 1)2. Click [Flag] and select True.
3. Result.
The data left is 2020.11.1-2022.11.1
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Wouldn't this be easier to do on SQL side; i.e. bring the filtered data from SQL directly rather than asking PQ to do the filtering which can be done on server side.
select * from [dbo].[transaction] where activity_date>=DATEADD(YEAR,-1,DATEADD(DAY,1,EOMONTH(GETDATE(),-1))) and activity_date<=DATEADD(YEAR,1,DATEADD(DAY,1,EOMONTH(GETDATE(),-1)))Sorry that is what I meant to say, I am using a SQL Query to do this. The one thing is I can't use EOMONTH as I am still on an older 2008 SQL Server. Thank you.
MP-iCONN PQ equivalent then
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ1NNQ1VIrVAfGMDFB4hig8IxSeMZQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] >= Date.From(Date.AddYears(Date.StartOfMonth((DateTime.LocalNow())),-1)) and [Column1] <= Date.From(Date.AddYears(Date.StartOfMonth((DateTime.LocalNow())),1))) in #"Filtered Rows"
I went ahead and figured this out with this query line:
WHERE [Sales Orders].[Required Date] BETWEEN dateadd(month,datediff(month,0,getdate())-12,0) AND dateadd(month,datediff(month,0,getdate())+12,0)
This got me 11/1/2020 to 11/1/2022
Thank you for your help.
Hi MP-iCONN ,
Here are the steps you can follow：
1. Add Column – Custom Column.
[date] >= #date(Date.Year(DateTime.LocalNow()) -1 , Date.Month(Date.StartOfMonth(DateTime.LocalNow())), 1) and [date] <=#date(Date.Year(DateTime.LocalNow()) +1 , Date.Month(Date.StartOfMonth(DateTime.LocalNow())), 1)2. Click [Flag] and select True.
3. Result.
The data left is 2020.11.1-2022.11.1
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nice solution. I tried this just to see how it worked and will use this in the future. Thank you.
