Forum Discussion

kelly49's avatar
kelly49
Frequent Visitor
9 years ago
Solved

Convert YYYYWW from string to date for time series functions

My sales data comes in a string format of YYYYWW and I need to fix it for time series calculations.     I was able to convert it in SQL to the first day of the week beginning on January 1, but 2016...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    kelly49,

     

    You may use DAX below to add a calculated column.

    WeekEndDate =
DATE ( VALUE ( LEFT ( Table1[YYYYWW], 4 ) ), 1, 1 )
    + VALUE ( RIGHT ( Table1[YYYYWW], 2 ) ) * 7
    - 1
kelly49's avatar
kelly49
Frequent Visitor
9 years ago

Thank you for replying so quickly!  I ended up using if else in the SQL server function for leap year weeks inspired by:

 

WeekEndDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7) + 6 

 

 

If anyone has an easier way in DAX, that would be appreciated because as of now I will have to run the SQL function every time my data updates. 

 

 

I'll mark your answer as solved if no one else answers with a DAX solution by tomorrow.  

v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
9 years ago

kelly49,

 

You may use DAX below to add a calculated column.

WeekEndDate =
DATE ( VALUE ( LEFT ( Table1[YYYYWW], 4 ) ), 1, 1 )
    + VALUE ( RIGHT ( Table1[YYYYWW], 2 ) ) * 7
    - 1
  • kelly49's avatar
    kelly49
    Frequent Visitor
    9 years ago

    Yes, I ended up using:

     

    = date(left('CR dimCalendar'[IntervalKey],4),1,1 
+ right('CR dimCalendar'[IntervalKey],2)*7-7)

     to get the first date of the week.

     

    Unfortunately, my real problem seems to be the inability to use timevalue functions at a weekly grain (without contiguous dates in the fact table) :/