Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
8 years ago
Solved

Create date columm from odd text format

Hi all,   I'm running into an issue trying to create a date from an odd format.   From the below table, we have Year, Week Num of Year, and Day of Week Num. I need to turn the first row into Febr...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI RMDNA

     

    Please give this calculated column a try

     

    Column = 
VAR BaseDate = DATE(2018,2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

It should be easy enough to generate dates from those columns for 2018, I'm just worried about 2019.  Can I assume that it will start on the 1st of Feb and in that case - and I note that the 1st of Feb will be a Friday in 2019, will that sill be WeekDayNumber = 1?

RMDNA's avatar
RMDNA
Solution Sage
8 years ago

At least for the purposes of this example and getting a starting point, we can limit it to 2018 for now. Sounds like you might have an idea for that?

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI RMDNA

     

    Please give this calculated column a try

     

    Column = 
VAR BaseDate = DATE(2018,2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7

     

    • RMDNA's avatar
      RMDNA
      Solution Sage
      8 years ago

      That seems to have worked. Scaling it out to additional years will be a future issue, but this should do for now. Thanks for the support.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi RMDNA

         

        If I understand  your data correctly, scaling it out to other years could be as easy as the following tweak

         

        Column = 
VAR BaseDate = DATE('Table 2'[Year],2,1)
RETURN 
    BaseDate + 
    ('Table 2'[DayOfWeekNum]-1) +
    ('Table 2'[WeekNum] * 7) - 7