How to get first-day-of-week from week # ?

[sherifffruitfly]

Hi all, I have the datestream calendar table from the azure market. it has columns WeekNo, Year, and Date. I would like to add a calculated column for FirstDayOfWeek (datetime type). It seems like I should be able to filter on Year = (current row year) and WeekNo = (current row week), and then minimize the date. But I can't seem to come up with a formula that actually does this. Little help? thanks much, sff

 

[scottsen]

This is WeekEnding, but likely helpful?
Week Ending Date Calculation « PowerPivotPro

 

[Legacy 286899]

Not sure if this is what you're looking for, but the formula "=MOD(WEEKDAY("date"),7)=1" will get you the first day of the week (Sunday).

 

[sherifffruitfly]

got it - thanks much both of you.

i adjusted the end of week example linked above as follows, to begin on monday, end on sunday:

DayMod7 = MOD([DateKey] - 2, 7)

StartOfWeek = [DateKey] - [DayMod7]

EndOfWeek = [DateKey] - [DayMod7] + 6


thanks again all,
sff