cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DevadathanK
Resolver I
Resolver I

Obtain Dates from Weeknumber

Hi everyone!

<Message deleted>

Thank you for all the help!

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @DevadathanK ,

 

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
ADDCOLUMNS(
    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):

image.png

 

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

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi @DevadathanK

 

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
Did I answer your question? Mark my post as a solution!

lbeneyze
Advocate II
Advocate II

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 
                Date.AddDays(FirstDayofyear, FirstDayofyearDayofWeek + ((Week-2)*7))  

        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 
                Date.AddDays(FirstDayofyear, FirstDayofyearDayofWeek + ((Week-2)*7)+6)  

        in
            theDate
in
    getEndDateFromWeekinYear

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

 

Good luck.

 

Kind regards,

Lohic Beneyzet

 

 

 

TomMartens
Super User
Super User

Hey @DevadathanK ,

 

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
ADDCOLUMNS(
    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):

image.png

 

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

View solution in original post

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?
 
Thanks for all your help!!

Hey @shubhy04 ,

 

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
amitchandak
Super User
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

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
lbeneyze
Advocate II
Advocate II

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 my answer solves your question, please mark it as a solution.

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

Thanks in advance and good luck.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.