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
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) :/