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 (leap year) messes up the dates so that SAMEPERIODLASTYEAR() only works until February 26.

 

This is the SQL code to set the first week day, but I would prefer to do it in PowerBI or Visual Studio.

CREATE FUNCTION [dbo].[Week] (@yyyyww char(6))
RETURNS datetime
AS
BEGIN
DECLARE @StartDatetime date
DECLARE @yyyy char(4) = Left(@yyyyww,4)
DECLARE @ww tinyint = Right(@yyyyww,2)
SET @StartDatetime = Cast(@yyyy as datetime) - 
day(Cast(@yyyy  as datetime)) + 1 + (@ww - 1) * 7 

RETURN @StartDatetime
END
GO

 

Is there a better way to do this or something I can do about the leap year problem? 

 

 

 

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

4 Replies

  • Rich_P's avatar
    Rich_P
    Icon for Helper II rankHelper II
    9 years ago

    Maybe not exactly what you were hoping for, but I have had pretty good luck with these functions (VBA) over the years. I can't remember where I came across them, or I would give proper credit. But it might get you going int he right direction...

     

    Rich P

     

    Option Explicit

Type typDateBlock
    WeekNumber As Integer
    StartDate As Date
    EndDate As Date
End Type


Public NewDateBlock As typDateBlock

Public Function GetDateBlockByWeek(intWeekNum As Integer, intYearNum As Integer)

    With NewDateBlock
        .StartDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7)
        .EndDate = .StartDate + 6
        .WeekNumber = ISOWeekNum(.StartDate)
        
        Debug.Print .WeekNumber, .StartDate, .EndDate
    End With
     
End Function

Public Function GetDateBlockByDate(dteGivenDate As Date, Optional WhichFormat As Variant)
    Dim ThisYear As Integer
    Dim PreviousYearStart As Date
    Dim ThisYearStart As Date
    Dim NextYearStart As Date
    Dim intYearNum As Integer

    ThisYear = Year(dteGivenDate)
    ThisYearStart = YearStartDate(ThisYear)
    PreviousYearStart = YearStartDate(ThisYear - 1)
    NextYearStart = YearStartDate(ThisYear + 1)
    
    With NewDateBlock
        Select Case dteGivenDate
            Case Is >= NextYearStart
                .WeekNumber = (dteGivenDate - NextYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate) + 1
            Case Is < ThisYearStart
                .WeekNumber = (dteGivenDate - PreviousYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate) - 1
            Case Else
                .WeekNumber = (dteGivenDate - ThisYearStart) \ 7 + 1
                intYearNum = Year(dteGivenDate)
        End Select

        .StartDate = YearStartDate(intYearNum) + ((.WeekNumber - 1) * 7)
        
        .EndDate = .StartDate + 6
        
        Debug.Print .WeekNumber, .StartDate, .EndDate, intYearNum
    End With
     
End Function


Public Function YearStartDate(intYearNum As Integer) As Date
    Dim WeekDay As Integer, NewYear As Date
    
    NewYear = DateSerial(intYearNum, 1, 1)
    WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
    
    If WeekDay < 4 Then
        YearStartDate = NewYear - WeekDay
    Else
        YearStartDate = NewYear - WeekDay + 7
    End If

End Function
     
Public Function WeekStartDate(intWeekNum As Integer, intYearNum As Integer) As Date

    WeekStartDate = YearStartDate(intYearNum) + ((intWeekNum - 1) * 7)

End Function

Public Function WeekEndDate(intWeekNum As Integer, intYearNum As Integer) As Date

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

End Function


Public Function ISOWeekNum(dteGivenDate As Date, Optional WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'                                = 2 then YYWW
'
    Dim ThisYear As Integer
    Dim PreviousYearStart As Date
    Dim ThisYearStart As Date
    Dim NextYearStart As Date
    Dim YearNum As Integer
    
    ThisYear = Year(dteGivenDate)
    ThisYearStart = YearStartDate(ThisYear)
    PreviousYearStart = YearStartDate(ThisYear - 1)
    NextYearStart = YearStartDate(ThisYear + 1)
    
    Select Case dteGivenDate
        Case Is >= NextYearStart
            ISOWeekNum = (dteGivenDate - NextYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate) + 1
        Case Is < ThisYearStart
            ISOWeekNum = (dteGivenDate - PreviousYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate) - 1
        Case Else
            ISOWeekNum = (dteGivenDate - ThisYearStart) \ 7 + 1
            YearNum = Year(dteGivenDate)
    End Select
    
    If IsMissing(WhichFormat) Then Exit Function
    
    If WhichFormat = 2 Then
        ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & Format(ISOWeekNum, "00"))
    End If

End Function
    • 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