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

     

     

7 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        TomMartens
        Super User
        6 years ago

        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's avatar
    amitchandak
    Super User
    6 years ago

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

     

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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!