Weekly Date Calculation

[ctedesco3307]

In the calendar table structure below the Billing Friday column is meant to identify the friday of that week the Date falls on, for billing cycle purposes....

What I need it to do is choose the prior friday for the saturday dates, but the following friday for all others. So in short, include saturday in the current bill week

How do I fix the return for that? In this current formula, for example, 1/2/2021 returns 1/8/2021 and it should return 1/1/2021

Thanks very much in advance

------------------------------------------------------------------------------------------------------------

TTDate = ADDCOLUMNS(

CALENDAR(date(2021,1,1), date(2022,12,31)),

"Month", FORMAT([Date],"mmm YY"),

"Year",YEAR([Date]),

"Week", WEEKNUM([Date]),

"WeekDay", WEEKDAY ( [Date], 1 ),

"Billing Friday",

VAR DayNumber =

WEEKDAY ( [Date], 1 )

RETURN

[Date] + ( 6 - DayNumber ) + 7 * ( DayNumber > 6 )

)

[ctedesco3307]

@freginier - This was perfect! Thank you 

[freginier · Accepted solution]

Try this and tell me

TTDate = ADDCOLUMNS(
CALENDAR(date(2021,1,1), date(2022,12,31)),
"Month", FORMAT([Date],"mmm YY"),
"Year",YEAR([Date]),
"Week", WEEKNUM([Date]),
"WeekDay", WEEKDAY ( [Date], 1 ),
"Billing Friday",
VAR DayNumber =
WEEKDAY ( [Date], 1 )
RETURN
IF(DayNumber = 7,[Date] - 1, [Date] + 6 - DayNumber)
)