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

[SonaSinghA]

Hi,

How can I get last Friday date based on Today in a Custom Column of Power Query please?
I tried the below solution, but if today is Friday, then the formula is giving today's date instead of last Friday.

Date.AddDays([DateColumn],-Date.DayOfWeek([DateColumn],5)))

Thanks

[belvoir99 · Accepted solution]

Hi @SonaSinghA 

CurrentDate = DateTime.Date( DateTime.LocalNow() ),
LastFridayColumn = Table.AddColumn(LastStepName, "LastFriday", each if Date.DayOfWeek(CurrentDate) >= 5 then Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3 + 7) else Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3), type date)

The default DayOfWeek is zero for Monday to 6 Sunday.

EDIT: today is Friday - this will choose the previous Friday, 29 March. You'll have to modify the formula slightly if you want today to return today.

[belvoir99 · Accepted solution]

Hi @SonaSinghA 

CurrentDate = DateTime.Date( DateTime.LocalNow() ),
LastFridayColumn = Table.AddColumn(LastStepName, "LastFriday", each if Date.DayOfWeek(CurrentDate) >= 5 then Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3 + 7) else Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3), type date)

The default DayOfWeek is zero for Monday to 6 Sunday.

EDIT: today is Friday - this will choose the previous Friday, 29 March. You'll have to modify the formula slightly if you want today to return today.

[SonaSinghA · Accepted solution]

Thank you @belvoir99, it worked for me 
= Table.AddColumn(#"Filtered Rows4", "LastFriday", each if Date.DayOfWeek(DateTime.Date( DateTime.LocalNow() )) >= 5 then Date.AddDays(DateTime.Date( DateTime.LocalNow() ), -Date.DayOfWeek(DateTime.Date( DateTime.LocalNow() )) - 3 + 7) else Date.AddDays(DateTime.Date( DateTime.LocalNow() ), -Date.DayOfWeek(DateTime.Date( DateTime.LocalNow() )) - 3), type date)

[belvoir99]

Hi @SonaSinghA above is the code you need to add in Power Query. Go to Transform Data > Advanced Editor, add a comma to the last step before the 'in' clause, then paste in the code -- something like this beforehand:

TheLastLine = Something Here
in
TheLastLine

and this afterwards:

TheLastLine = Something Here,
CurrentDate = DateTime.Date( DateTime.LocalNow() ),
LastFridayColumn = Table.AddColumn(TheLastLine, "LastFriday", each if Date.DayOfWeek(CurrentDate) >= 5 then Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3 + 7) else Date.AddDays(CurrentDate, -Date.DayOfWeek(CurrentDate) - 3), type date)
in
LastFridayColumn

things to note: TheLastLine step name is referenced in the LastFridayColumn step

if you are still not sure what to do, then paste the last few lines of code from your table and I'll post back final code.  

[Ritaf1983]

Hi @SonaSinghA 
Try to modify the formula to :
Date.AddDays(Date.From(DateTime.LocalNow()),-Date.DayOfWeek(Date.From(DateTime.LocalNow()),5)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[SonaSinghA]

@Ritaf1983 , still same