Forum Discussion

ctedesco3307's avatar
ctedesco3307
Resolver II
4 years ago
Solved

Weekly Date Calculation

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 )
 
)
Need Help
  • freginier's avatar
    freginier
    4 years ago

    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)
)

     

2 Replies

  • freginier's avatar
    freginier
    Solution Sage
    4 years ago

    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)
)