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
mb0307
Responsive Resident
Responsive Resident

Last Friday date

Hi,

 

How can I get last Friday date in a Custom Column of Power Query please?

 

Today is 28/07/2021 so last Friday date was 23/07/2021.

 

Thanks

1 ACCEPTED SOLUTION
mb0307
Responsive Resident
Responsive Resident

Found a solution on Desktop forum:

Date.AddDays(Date.From(DateTime.LocalNow()),-Date.DayOfWeek(Date.From(DateTime.LocalNow()),5))

View solution in original post

5 REPLIES 5
mb0307
Responsive Resident
Responsive Resident

Found a solution on Desktop forum:

Date.AddDays(Date.From(DateTime.LocalNow()),-Date.DayOfWeek(Date.From(DateTime.LocalNow()),5))

mahoneypat
Employee
Employee

Here is one way to do it in a custom column.

 

= if Date.DayOfWeek([Date]) >= 5 then Date.AddDays(Date.StartOfWeek([Date]), 5) else Date.AddDays(Date.StartOfWeek([Date]), -2)

 

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


CNENFRNL
Community Champion
Community Champion

= Date.StartOfWeek(Date.From(DateTime.LocalNow())) - #duration(2,0,0,0)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

mb0307
Responsive Resident
Responsive Resident

@CNENFRNL  thanks but not what I wanted.

 

This is giving me date of 2 days ago i.e. 24/07/2021 which was Saturday.  Can it be dynamic to calculate from any date what was last Friday date? 


Thanks 

And here is another way.

 

= List.Max(List.Select(List.Dates(Date.AddDays([Date], -6),7,#duration(1,0,0,0)), each Date.DayOfWeek(_) = 5))

 

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.

Top Solution Authors
Top Kudoed Authors