Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.