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
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?
Solved! Go to Solution.
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
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.
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
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) 😕
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |