cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imranamikhan
Helper V
Helper V

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

 

 
 
1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
imranamikhan
Helper V
Helper V

Thanks Pat. I suspect I have messed up the syntax here? The name of the Date column is "d".

 

Capture.PNG

 

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

    Custom1

  

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

 

Solution.png

@imranamikhan  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())*/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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.

@imranamikhan  sure no worries





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


imranamikhan
Helper V
Helper V

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors