Convert YYYYWW from string to date for time series functions

[kelly49]

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? 

[Rich_P]

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]

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 · Accepted solution]

@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]

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) 😕