Forum Discussion
Power Query Last 7 Days From Max Date Column
Hi everyone,
I want to use Power Query to add a custom column which identifies whether a date value from a date column is "within" or "outside" the last 7 days from the maximum date in the date column.
Essentially I want to modify the below code from mahoneypat & Fowmy, but instead of filtering the table to the last 7 days from the max date, I want to add a column as described above.
= Table.SelectRows(#"Changed Type", let latest = List.Max(#"Changed Type"[Date]) in each ([Date] <= latest) and ([Date] >= Date.AddDays(latest,- 7) ))
https://community.powerbi.com/t5/Power-Query/Filter-Date-for-Last-7-Days-of-Data/td-p/2214000
Best regards,
ImranAmi
Sorry. Forgot that part. Here you go.
let maxdate = List.Max(#"Previous Step"[Date]) in if [Date] >= Date.AddDays(maxdate, -7) then "Y" else "N"
Pat
9 Replies
You can add a custom column with an expression like this. Replace [Date] with your date column
= if [Date] >= Date.AddDays(Date.From(DateTime.LocalNow()), -7) then "Y" else "N"
Pat
Thanks for the speedy response Pat. Is there a method to calculate by the MAX date from the list of dates in the Date column, as opposed to using NOW? My dataset is a weekly snapshot so the MAX date would not be TODAY.
ImranAmi
Sorry. Forgot that part. Here you go.
let maxdate = List.Max(#"Previous Step"[Date]) in if [Date] >= Date.AddDays(maxdate, -7) then "Y" else "N"
Pat
Thanks Pat. I suspect I have messed up the syntax here? The name of the Date column is "d".
let Source = Sql.Database("xxx", "xxx", [Query="select * from ##dates"]), #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"d", type date}}, "en-GB"), Custom1 = Table.AddColumn(#"Changed Type with Locale", "IsLast7Days", let maxdate = List.Max(#"Changed Type with Locale"[d]) in if [d] >= Date.AddDays(maxdate, -7) then "Y" else "N") in Custom1imranamikhan if you intend to bring only last 7 days of data from the SQL server, it will be lot easier and performant to do on the server-side
/*TSQL Query to get only data from last 7 days*/ select * /*, CASE WHEN gl_date>=DATEADD(DAY,-7,GETDATE()) then 'within last 7days' else 'outside of last 7days' end as filter*/ from [dbo].[Actuals] where gl_date>=DATEADD(DAY,-7,GETDATE()) /*TSQL Query to identify if the data is from last 7 days*/ select * , CASE WHEN gl_date>=DATEADD(DAY,-7,GETDATE()) then 'within last 7days' else 'outside of last 7days' end as filter from [dbo].[Actuals] /*where gl_date>=DATEADD(DAY,-7,GETDATE())*/Thanks for the suggestion smpa01 but there is a specific reason I want to use Power Query as opposed to delegating back to SQL server.
I was complicating things - now corrected by adding Pat's formula to a custom column.
let maxdate = List.Max(#"Changed Type with Locale"[d]) in if [d] >= Date.AddDays(maxdate, -7) then 1 else 0
For others who still got this concern, I found a better way to do the same purpose: Date.IsInPreviousNDays - PowerQuery M | Microsoft Learn
