cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Resolver I

## Obtain Dates from Weeknumber

Hi everyone!

<Message deleted>

Thank you for all the help!

1 ACCEPTED SOLUTION
Super User

I guess it's almost impossible without additional information like the year, or it is very much simplified. If you assume that week one starts always with the 1st of January.

For the latter you can use this DAX statement to create a calculated column that creates the startdate:

``````StartDate =
var _year = 2020
return
DATE(_year , 1 , 1) + ('Table'[weeknum] - 1 ) * 7``````

and this to create a calculated column that represents the enddate:

``````EndDate =
var _year = 2020
return
DATE(_year , 1 , 1) + ('Table'[weeknum] - 1 ) * 7 + 6``````

In addition, you can consider using a Date Table that is not related, this DAX creates a Date table for the year 2020:

``````Simple Date =
var DateStart = DATE(2020 , 1 , 1)
var DateEnd = DATE(2020 , 12 , 31)
return
CALENDAR(DateStart , DateEnd)
, "weeknum", WEEKNUM(''[Date] , 2) //week begins on Monday
, "weeknum iso" , WEEKNUM(''[Date] , 21) //returns the weeknum based on ISO 8601
)``````

Now you can use this DAX Statement to find the starting date of the week in your existing table:

``````Startdate Calendar =
var __Weeknum = 'Table'[weeknum]
return
CALCULATE(MIN('Simple Date'[Date]) ,  'Simple Date'[weeknum] = __Weeknum)``````

and this to find the enddate:

``````Enddate Calendar =
var __Weeknum = 'Table'[weeknum]
return
CALCULATE(MAX('Simple Date'[Date]) ,  'Simple Date'[weeknum] = __Weeknum)``````

Here is a screenshot of the resulting table (the weeknum iso column can be used accordingly):

Hopefully, this provides what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
7 REPLIES 7
Community Support

I agree with @TomMartens ,you need first to define a date table ,then you can use vlookup function to create a table as you need.

Best Regards,
Kelly

Hi,

Personally, I prefer to do these kind of transformations in PowerQuery instead of DAX.
I've written 2 PowerQuery functions based on http://excel-inside.pro/blog/2018/03/06/iso-week-in-power-query-m-language-and-power-bi/:

- getStartDateFromWeekinYear: return the 1st day of week based on first day of a year and take as input a week in format YYYY-W## (i.e. 2020-W01, 2020-W52)

- getEndDateFromWeekinYear: return the last day of a week based on last day in year and take as input a week in format YYYY-W## (i.e. 2020-W01, 2020-W52)
- with those 2 dates you can add column based on date button "Substract days" and adding 1 (day)

getStartDateFromWeekinYear

``````let
getStartDateFromWeekinYear = (inputWeek as text) as date =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Sunday)
in
result,

//get the year from the inputWeek
WeekYear = Number.FromText(
Text.Range(inputWeek, 0, 4)
),

//get the weeknumber from the inputWeek
Week = Number.FromText(
Text.Range(inputWeek, 6, 2)
),

FirstDayofyear =  #date(WeekYear,1,1),

FirstDayofyearDayofWeek = getDayOfWeek(FirstDayofyear),

LastDayofyear =  #date(WeekYear,12,31),

//if week is 1 then week start automatically on 1-1-year
theDate =
if
Week = 1
then
FirstDayofyear
else
//if week is last week of year then ???
//if other week then

in
theDate
in
getStartDateFromWeekinYear``````

getEndDateFromWeekinYear

``````let
getEndDateFromWeekinYear = (inputWeek as text) as date =>
let
getDayOfWeek = (d as date) =>
let
result = 1 + Date.DayOfWeek(d, Day.Sunday)
in
result,

//get the year from the inputWeek
WeekYear = Number.FromText(
Text.Range(inputWeek, 0, 4)
),

//get the weeknumber from the inputWeek
Week = Number.FromText(
Text.Range(inputWeek, 6, 2)
),

FirstDayofyear =  #date(WeekYear,1,1),

FirstDayofyearDayofWeek = getDayOfWeek(FirstDayofyear),

LastDayofyear =  #date(WeekYear,12,31),

//if week is 1 then week start automatically on 1-1-year
theDate =
if
Date.AddDays(FirstDayofyear, FirstDayofyearDayofWeek + ((Week-2)*7)+6) > LastDayofyear
then
LastDayofyear
else
//if week is last week of year then ???
//if other week then

in
theDate
in
getEndDateFromWeekinYear``````

Good luck.

Kind regards,

Lohic Beneyzet

Super User

I guess it's almost impossible without additional information like the year, or it is very much simplified. If you assume that week one starts always with the 1st of January.

For the latter you can use this DAX statement to create a calculated column that creates the startdate:

``````StartDate =
var _year = 2020
return
DATE(_year , 1 , 1) + ('Table'[weeknum] - 1 ) * 7``````

and this to create a calculated column that represents the enddate:

``````EndDate =
var _year = 2020
return
DATE(_year , 1 , 1) + ('Table'[weeknum] - 1 ) * 7 + 6``````

In addition, you can consider using a Date Table that is not related, this DAX creates a Date table for the year 2020:

``````Simple Date =
var DateStart = DATE(2020 , 1 , 1)
var DateEnd = DATE(2020 , 12 , 31)
return
CALENDAR(DateStart , DateEnd)
, "weeknum", WEEKNUM(''[Date] , 2) //week begins on Monday
, "weeknum iso" , WEEKNUM(''[Date] , 21) //returns the weeknum based on ISO 8601
)``````

Now you can use this DAX Statement to find the starting date of the week in your existing table:

``````Startdate Calendar =
var __Weeknum = 'Table'[weeknum]
return
CALCULATE(MIN('Simple Date'[Date]) ,  'Simple Date'[weeknum] = __Weeknum)``````

and this to find the enddate:

``````Enddate Calendar =
var __Weeknum = 'Table'[weeknum]
return
CALCULATE(MAX('Simple Date'[Date]) ,  'Simple Date'[weeknum] = __Weeknum)``````

Here is a screenshot of the resulting table (the weeknum iso column can be used accordingly):

Hopefully, this provides what you are looking for.

Regards,
Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

Hi @TomMartens ,

I am facing same issue to create a date from week number. Used your proposed solution in the below formula where it should give either start/end date based on filter value selected.

*

Date=

Var Test1= DATE(2020, 1 , 1) + (MAX('Table'[Week Number]) - 1 ) * 7

Var Test2= DATE(2020, 1 , 1) + (MAX('Table'[Week Number]) - 1 ) * 7+6

VAR SelectMeasure =
MIN ( 'Date Slicer'[Order])

RETURN
IF (
HASONEVALUE ( 'Date Slicer'[Period]),

SWITCH ( SelectMeasure,
1, Test1,
2, Test2
)
)
*

Now, the issue im facing is: while your proposed function works fine when used in an individual measure but gives output as a numeric value (44.00K) when i use it like this.

Could you please le me know any solution around this?

Super User

Hey @Anonymous ,

I have to admit that I do not understand your requirements, and how you use the approach from my solution.

Please take the time and create a pbix that contains sample data, but still reflects your data model. Upload the file to onedrive or dropbox and share the link. If you are using an xlsx to create the sample data, share the xlsx as well.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Super User

With week no and year you can calculate week start and end date

Week start =
var _minYear =date(year(mid([column],3,4))1,1)
var _maxYear =date(year(right([column],4))1,1)
return
Week = ((_minYear +(-1*weekday(_minYear)+1)) +7*left([column],2))

Week end=
var _minYear =date(year(mid([column],3,4))1,1)
var _maxYear =date(year(right([column],4))1,1)
return
((_maxYear +(-1*weekday(_maxYear)+1)) +7*mid([column],10,2))

With endofyear ans startofyear, you can control these dates going beyond year

Good morning,

You can use the power query functions here: http://excel-inside.pro/blog/2018/03/06/iso-week-in-power-query-m-language-and-power-bi/

Based on those, you can provide an ISO week input and get the first date of a week by providing 1st day (2020-W01-1) and 7th day. (2020-W01-7).

If you like my asnwer, you can also add a kudo 👍

Thanks in advance and good luck.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors