Forum Discussion
Calculating Week Start Date from Week Number
Hi there,
I have data by week number like this:
but I need to add a column that will show me the week end date like this format:
The other complication is that the week numbers are set to Sunday to Saturday instead of Monday to Sunday which is what I need. I have the daily dates in as well so maybe it's easier to make a calculation off that instead?
Do you know the easiest way to create a new column or measure to get this? Thank you!
14 Replies
Hey jnrezk ,
here I provide a solution that calculates the Start and End-Date for a week:
Solved: Re: Week commencing in DAX - Microsoft Power BI Community
In combination with the following calculations
ISOWEEK = WEEKNUM([date] , 21)
ISO YEAR
=IF([ISOWeek]<5 && [CalendarWeek] > 50;
[Year]+1;
IF([ISOWeek]>50 && [CalendarWeek]<5;
[Year]-1;
[Year]))in combination with a calendar table, you can use the calendar table as a lookup table to determine the week End Date.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
sorry if im slow and dont follow but its not working - are you saying to create 2 measures first
HI jnrezk,
You can use the following calculated column formula to get the start date based on the year and week number:
WeekStart = MINX( FILTER ( CALENDAR ( DATE ( LEFT ( [YearWeekNum], 4 ), 1, 1 ), DATE ( LEFT ( [YearWeekNum], 4 ), 12, 31 ) ), WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( [YearWeekNum], 2 ) ) ), [Date] )If you are looking for a measure version, you can add a variable to extract the current 'year week number' to calculate:
WeekStart = VAR cYW = MAX ( Table[YearWeekNum] ) RETURN MINX ( FILTER ( CALENDAR ( DATE ( LEFT ( cYW, 4 ), 1, 1 ), DATE ( LEFT ( cYW, 4 ), 12, 31 ) ), WEEKNUM ( [Date], 1 ) = VALUE ( RIGHT ( cYW, 2 ) ) ), [Date] )Regards,
Xiaoxin Sheng
Hi Im so sorry for the delay. I am trying the column method and having some errors:
And this was my code how I entered it in:
Hey jnrezk ,
the solution provided is based on creating a calculated column using DAX. From the code you provided it's obvious that you are using Power Query / M. This explains why MINX (a DAX function is raising an error).
Create a calculated column using DAX instead of using Power Query.
Regards,
Tom
The source is google analytics and they only provideo ISOWeeknumber which i need as a date format
Hi jnrezk,
Can you please share a pbix file with some dummy data and expected results to test coding fromula?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
here's a sample PBI file - as you can see week 26 should show as june 28th but its showing the week earlier. Also, I'd love the forumular for week ending so week 26 week end date is Sunday, July 4th
Thanks!https://drive.google.com/file/d/1yVGwX_iMSKKPQPbaePE_ytaqOtQ7qmEi/view?usp=sharing
HI jnrezk,
I modify the optional format to '2' and add the offset '-1' to the expression, then it will get the Sunday as result from the 'Monday to Sunday' week regular:
WeekStart = MINX( FILTER ( CALENDAR ( DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 1, 1 ), DATE ( LEFT ( [ISO Week of ISO Year], 4 ), 12, 31 ) ), WEEKNUM ( [Date], 2 ) = VALUE ( RIGHT ( [ISO Week of ISO Year], 2 ) ) ), [Date]-1 )Regards,
Xiaoxin Sheng
