Calculate week ending dates based off date column

[Anonymous]

I have a column called "date created" that contains some pretty standard dates like: 04/05/2023

I want to create a new column that contains the week ending dates (week ending on Friday) based off the dates in the "date created" column. So, if the date is 3/6/2023, then week ending date will be 3/10/2023. I've looked up various topics on here, but can't seem to find one that matches what I'm looking for.

Also, I found this link on here, but have no clue which areas I'm supposed to configure to make it work for me. It would be nice since it contains a number system, such as, W12 Week Ending 3/31/2018.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

Column = Data[Date]+5-WEEKDAY(Data[Date],2)

Hope this helps.

[Anonymous]

While it did work for some, I noticed two things:

1. for rows that were blank, it gave a date of 1/4/1900

2. Dates like 10/10/2021 (Sunday) had the Week Ending date of 10/8/2021, when it should be 10/15/2021 since the week ends on Friday.

Not sure how to fix this though...

[Ashish_Mathur]

Hi,

Does this calculated column formula work?

Column = if(ISBLANK(Data[Date]),BLANK(),Data[Date]+7-WEEKDAY(Data[Date],16))

[olgad]

Hi, do you have a calendar table already? This approach shows you how to create a calendar table. Go modeling, new table and copy paste that  dax formula and start changing the variables, lets say max and min date for the max and min dates you have. 

[Anonymous]

Hi. No, I don't have a calendar table. I wasn't aware I needed one since I had a column with dates already.

[olgad]

Any report needs a separate calendar table, because it has all the dates irrespective if there is a transaction on this date or not. 

[Anonymous]

I created a new table and copy and pasted the formula. But I'm still unsure of what values I need to change except min and max since those are straightforward.