Forum Discussion

TaufikMaggangka's avatar
TaufikMaggangka
Icon for Helper II rankHelper II
5 years ago
Solved

Weekly Date-Start Date and End Date

Hi All, I am trying to create weekly range column(from monday to saturday) using addtional column week start date and week end date, but then realized that the start date not started in first date...
Date Parameter
weekly
  • Anonymous's avatar
    Anonymous
    5 years ago

    TaufikMaggangka 
    I used those 4 columns to achieve your requirement, see pbix at the end.

     

    Basically I split the days into 5 groups with 6 days each because you want the new week to be Monday to Saturdays.

     

     

    Then I use Rankx to get the new weekday.\

    New weekday = RANKX(FILTER(Sheet1,[Year]=EARLIER([Year])&&[Month]=EARLIER([Month])&&[Day (bins)]=EARLIER([Day (bins)])),Sheet1[Day],,ASC)

     

     

    Best regards
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

TaufikMaggangka , Week that start with month start date and +7 days after that

 

Week Start at Month = eomonth([Date],-1)+0
Week No = QUOTIENT(datediff([Week Start at Month],[Date],day) ,7)+1
Week Start Date =[Week Start at Month] + QUOTIENT(datediff([Week Start at Month],[Date],day) ,7)*7
Week End Date = if([Week Start Date] +7 <=eomonth([Date],0), [Week Start Date] +7 ,eomonth([Date],0))

 

 

Conrolling the week

 

Start Month = STARTOMONTH('Date'[Date]) // or // eomonth([Date],-1)+0
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = if([Start Month]> [Date] -[WeekDay]+1 , [Start Month] , [Date] -[WeekDay]+1 )  //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

 

Week End date = if( eomonth([Date,0]) < 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2), eomonth([Date,0]) , 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2))