DATEADD function omitting values

[edaws]

I'm trying to create a calculated column that gives me the the date for Friday of the week a given date falls on.

here is my function:

=DATEADD('Table'[AcqDate],6-WEEKDAY('Table'[AcqDate]),DAY)

while this calculates some of the dates correctly, it omits some values for some reason (see entryid=3 below). I've checked the formats (and the time stamps in the AcqDate field to make sure they don't have something weird).

This is the first time I've tried to do anything with PowerPivot. I was initially excited when reading about the potential functionality but now seeing how buggy it might be, I'm much less enthusiastic about it. Any help would be greatly appreciated.

 

[kryptonian]

Try this:

=Sheet1[AcqDate]+(6-WEEKDAY(Sheet1[AcqDate],1))

WEEKDAY(Date,1) sets Sunday as 1, Monday as 2,..., Friday as 6. The formula adds X days to your AcqDate where X is the difference between Friday (6) and the day of the week for AcqDate.

 

[David Churchward]

Is your table ([AcqDate]) a list of sequential dates?

 

[Ben Niebuhr]

Are you looking for the Friday following the date, or the Friday of the week? The difference would be that your weekend may need to be backed up a day, where as all others would be moved forward.

If you would include Saturday in the next Friday then I would use:
='Table'[AcqDate]+7-weekday('Table'[AcqDate],16)

I have used this to great effect in the past, but for accumulating the dates on a Tuesday.

If you need to roll your Saturday back to the previous day this one works:
=IF(WEEKDAY('Table'[AcqDate],16)=1,'Table'[AcqDate]-1,'Table'[AcqDate]+7-(WEEKDAY('Table'[AcqDate],16)))

Not exactly PowerPivot-y, but either will work depending on your needs.

Ben