Forum Discussion
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 of the month as well as the week and date not end in last of the month. Here the dax i am used :
Start date :
Week Start Date = Sheet1[Date] - WEEKDAY(Sheet1[Date],2)+1end date:
Week End Date = Sheet1[Date] - WEEKDAY(Sheet1[Date],2)+6
Basically, I want to start date always in the first day of the month basically day 1(for instance 1 september) and end date in 31 or 30 (for instance 30 september).
Anyone can help with this, please also see my atatched pbix here :
https://drive.google.com/file/d/143dko9boxjZuBkhxBTQQlhOiWW0t3HEP/view?usp=sharing
I hope there were an expert can help, I am quite new in the Power BI.
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.
5 Replies
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)+1Week 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))
Thanks so much amitchandak ,
would you like to send me the pbix file. it will be really helpfull ?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.hi amitchandak ,
Thank you so much for help.
I try your suggestion based on the PBIX file that you send, it working fine until I got a first daay of the month that start in sunday. Please see image attached.
Basically, I need the start of the week for each monday is start from monday. Try to find any sollutions but stuck, but I found that maybe it can be fix it if start of the month dax is change to start from Monday.
Is it possible to change the query for the start month is start from monday.
