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

     

     

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