Obtain Dates from Weeknumber

[DevadathanK]

Hi everyone!

<Message deleted>

Thank you for all the help!

[v-kelly-msft]

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]

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 · Accepted solution]

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

Hopefully, this provides what you are looking for.

Regards,
Tom

[Anonymous]

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

[TomMartens]

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

[amitchandak]

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

[lbeneyze]

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.