Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kelly49
Frequent Visitor

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? 

 

 

 

1 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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Rich_P
Helper II
Helper II

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

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.  

@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
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.