Forum Discussion
Helper IIWeekly Date-Start Date and End Date
TaufikMaggangka , Please find the attached file. with some minor changes
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.
Super UserTaufikMaggangka , 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))
Helper IIThanks so much amitchandak ,
would you like to send me the pbix file. it will be really helpfull ?
- amitchandak
TaufikMaggangka , Please find the attached file. with some minor changes