Forum Discussion

DevadathanK's avatar
DevadathanK
Resolver I
6 years ago
Solved

Obtain Dates from Weeknumber

Hi everyone! <Message deleted> Thank you for all the help!
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    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

     

     

lbeneyze's avatar
lbeneyze
Advocate II
6 years ago

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