Need to calculate the week-ending dates for the complete year.

[hxkresl]

In my Date (or calendar) table I need a column having the week ending dates on Friday.

Similar to Excel:

https://excel.tips.net/T002444_Calculating_Week-Ending_Dates.html 

If someone has previously solved/pls share link.

[Anonymous]

The weekending calculations here are WRONG!

I wanted to get a weekending of Friday. So for me my week was from Sat 00:00 to Fri 23:59. This is the RIGHT formula for it

Week_Ending6 = [creation_date] - MOD([creation_date]+0|7)+6

[Hasan · Accepted solution]

I have used this formula before and it worked for me

Weekend = 'Calendar'[DT]-MOD('Calendar'[DT]-1,7)+5

[sirlanceohlott]

@Hasan Thank you for the great solution!

[MGutt]

I used your formula to calculate my week ending for Thursdays, however in some cases the week ending date is before the completion date:

MG_WeekEnding = 'MG_WIP'[CompletionDate]-MOD('MG_WIP'[CompletionDate]-1,7)+4

[Hasan]

HI There,

I played around with dummy data and i found this:

If completion date is Friday, with this formula week ending will be last thursday. I think you need to decide if you want to roll over Friday to next week or the current actual week

[MGutt]

I have used this to calculate my week ending on Thursdays, however sometimes the calculated week ending date is before the completion date:

MG_WeekEnding = 'MG_WIP'[CompletionDate]-MOD('MG_WIP'[CompletionDate]-1,7)+4

[hxkresl]

@Hasan  that's working well.

Thanks.