topic Re: Weekly Date-Start Date and End Date in DAX Commands and Tips

Weekly Date-Start Date and End Date

TaufikMaggangka — Wed, 03 Nov 2021 03:29:04 GMT

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

end 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.

Re: Weekly Date-Start Date and End Date

amitchandak — Wed, 03 Nov 2021 04:26:00 GMT

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

Re: Weekly Date-Start Date and End Date

TaufikMaggangka — Wed, 03 Nov 2021 06:43:23 GMT

Thanks so much amitchandak ,
would you like to send me the pbix file. it will be really helpfull ?

Re: Weekly Date-Start Date and End Date

amitchandak — Wed, 03 Nov 2021 07:09:08 GMT

TaufikMaggangka , Please find the attached file. with some minor changes

Re: Weekly Date-Start Date and End Date

Anonymous — Fri, 05 Nov 2021 03:22:57 GMT

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.

Re: Weekly Date-Start Date and End Date

TaufikMaggangka — Fri, 17 Jun 2022 06:49:44 GMT

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.