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

     

RMDNA's avatar
RMDNA
Solution Sage
8 years ago

February 1 2018 was a Thursday.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Cool, how would I know that normally?  Can I hardcode that into the logic, or does the DAX need to work out other years too.

 

How come you need to start from 1st Feb (and not 1st Jan?)... just curious

  • RMDNA's avatar
    RMDNA
    Solution Sage
    8 years ago

    The end goal of creating this date key to connect this table into my report's existing calendar table.  I'm using 2018 as an example, but the data for this particular table begins 2/1/2017.

     

    The client's fiscal year begins on February 1st, for whatever odd reason. It's definitely caused some reporting issues...

    • 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?