Power BI Weeknum Iteration

Question

My goal is to display the weeknum based on a modified 4-4-5 calendar starting on Sunday using DAX or m code if it makes more sense.

That means the first Sunday of January is Week 1 and all weeks should be full weeks (7 days). The end of 2018 had a 53rd week, which by definition would extend to Saturday, 1/5/2019 - the first Sunday of 2019 would be 1/6/2019 which is week 1. I have been unable to reset the weeknum function in each year. If my first few calendar days of the year were week 52 or 53, the following week should be 1 but instead starts at week 2.

Trying to subtract -1 weeknum through if statements, lookupvalue, etc always throws something off.

Using WEEKNUM(DATE,1) will give full weeks starting on Sunday unless it's the start of the year.

Using WEEKNUM(DATE,17) will also give full weeks starting on Sunday and then also extend the previous year end's week if it is not a full 7 days. ( Week 53 extending into calendar week 1 of next year).

The issue remains that going into next year, week 1 is either partially or entirely replaced by the previous year end's week. Basically, I can only get proper results for almost one year but not the 5+ years in the calendar table.

I also have columns for the date of week start, week end, and day of year (1 to 365/366). Any suggestions on logic to iterate the proper weeknum each year? Thanks.

Answer 1

I have similar requirement of financial week number, Period and dataid.

I created table in the database and same has been imported into poweri bi report.

my financial week start from March 2nd Sunday and financial week number for 10 years from this year.

below script might help you:

-- Create DI_Date diamension table:
CREATE TABLE APM_Reporting.di_date
(
     "datekey"              INTEGER     NOT NULL 
    ,"dateid"               DATE        NOT NULL  
    ,"day"                  INTEGER     NOT NULL
    ,"dayname"              VARCHAR(10) NOT NULL 
    ,"daynameshort"         VARCHAR(3)  NOT NULL
    ,"weekid"               INTEGER     NOT NULL  
    ,"financialweekid"      INTEGER     NOT NULL
    ,"monthid"              INTEGER     NOT NULL
    ,"monthname"            VARCHAR(25) NOT NULL 
    ,"periodid"             INTEGER     NOT NULL  
    ,"periodname"           VARCHAR(25) NOT NULL 
    ,"quarterid"            INTEGER     NOT NULL  
    ,"quartername"          VARCHAR(25) NOT NULL 
    ,"year"                 INTEGER     NOT NULL  
    ,"finyear"              VARCHAR(25) NOT NULL
    ,"created_timestamp"    DATETIME DEFAULT CURRENT_TIMESTAMP
    ,PRIMARY KEY (datekey)
);
-- Check if temp table exists and drop it if true
IF OBJECT_ID('tempdb..##Dates') IS NOT NULL
    DROP TABLE ##Dates
-- Create temporary table for pre-load
  CREATE TABLE ##Dates(
                DateValue Date 
                      )
;                      
-- Declare Start Date
  DECLARE @start DATE = GETDATE() - 396
  DECLARE @end DATE = DATEADD(year, 10,@start)
  WHILE @start < @end
  BEGIN
    INSERT INTO  ##Dates(DateValue)
    VALUES(@start)  
    SET @start = DATEADD(dd,1,@start)
  END
  ;
-- Insert generated data to di_date
INSERT INTO APM_Reporting.di_date 
(
    "datekey",
    "dateid",
    "day",
    "dayname",
    "daynameshort",
    "weekid",
    "financialweekid",
    "monthid",
    "monthname",
    "periodid",
    "periodname",
    "quarterid",
    "quartername",
    "year",
    "finyear"
)

SELECT 
    YEAR(DateValue)*10000+MONTH(DateValue)*100+DAY(DateValue) AS "datekey"
    ,DateValue "dateid"
    ,DAY(DateValue) "day"
    ,DATENAME(dw, DateValue) "dayname"
    ,LEFT(DATENAME(dw,DateValue),3) "daynameshort"
    ,DATEPART(WK,DateValue) "weekid"
    ,CASE
    WHEN DATEPART(WK,DateValue) < 11 THEN DATEPART(WK,DateValue)+42
    ELSE DATEPART(WK,DateValue) - 10
    END AS  "financialweekid"
    ,DATEPART(MM, DateValue) "monthid"
    ,DATENAME(MM,DateValue) "monthname"
    ,CAST(
    CONCAT(
        (CASE 
            WHEN DATEPART(WK,DateValue) BETWEEN 1 and 10 THEN DATEPART(YYYY,DateValue)-1
            ELSE DATEPART(YYYY,DateValue)
        END)
    ,
    CASE
        WHEN DATEPART(WK,DateValue)  BETWEEN '11' AND '14' THEN '01'
        WHEN DATEPART(WK,DateValue)  BETWEEN '15' AND '18' THEN '02'
        WHEN DATEPART(WK,DateValue)  BETWEEN '19' AND '22' THEN '03'
        WHEN DATEPART(WK,DateValue)  BETWEEN '23' AND '26' THEN '04'
        WHEN DATEPART(WK,DateValue)  BETWEEN '27' AND '30' THEN '05'
        WHEN DATEPART(WK,DateValue)  BETWEEN '31' AND '34' THEN '06'
        WHEN DATEPART(WK,DateValue)  BETWEEN '35' AND '38' THEN '07'
        WHEN DATEPART(WK,DateValue)  BETWEEN '39' AND '42' THEN '08'
        WHEN DATEPART(WK,DateValue)  BETWEEN '43' AND '46' THEN '09'
        WHEN DATEPART(WK,DateValue)  BETWEEN '47' AND '50' THEN '10'
        WHEN DATEPART(WK,DateValue)  BETWEEN '51' AND '53' THEN '11'
        WHEN DATEPART(WK,DateValue)  BETWEEN '01' AND '02' THEN '11'
        WHEN DATEPART(WK,DateValue)  BETWEEN '03' AND '06' THEN '12'
        WHEN DATEPART(WK,DateValue)  BETWEEN '06' AND '53' THEN '13'
    ELSE '00'
    END) AS INTEGER) AS "periodid"
    ,CONCAT('P',
    CASE
        WHEN DATEPART(WK,DateValue)  BETWEEN '11' AND '14' THEN '01'
        WHEN DATEPART(WK,DateValue)  BETWEEN '15' AND '18' THEN '02'
        WHEN DATEPART(WK,DateValue)  BETWEEN '19' AND '22' THEN '03'
        WHEN DATEPART(WK,DateValue)  BETWEEN '23' AND '26' THEN '04'
        WHEN DATEPART(WK,DateValue)  BETWEEN '27' AND '30' THEN '05'
        WHEN DATEPART(WK,DateValue)  BETWEEN '31' AND '34' THEN '06'
        WHEN DATEPART(WK,DateValue)  BETWEEN '35' AND '38' THEN '07'
        WHEN DATEPART(WK,DateValue)  BETWEEN '39' AND '42' THEN '08'
        WHEN DATEPART(WK,DateValue)  BETWEEN '43' AND '46' THEN '09'
        WHEN DATEPART(WK,DateValue)  BETWEEN '47' AND '50' THEN '10'
        WHEN DATEPART(WK,DateValue)  BETWEEN '51' AND '53' THEN '11'
        WHEN DATEPART(WK,DateValue)  BETWEEN '01' AND '02' THEN '11'
        WHEN DATEPART(WK,DateValue)  BETWEEN '03' AND '06' THEN '12'
        WHEN DATEPART(WK,DateValue)  BETWEEN '06' AND '53' THEN '13'
    ELSE '00'
    END) "periodname"
    ,DATEPART(Q,DateValue) "quarterid"
    ,CONCAT('Q',DATEPART(Q,DateValue)) "quartername"
    ,DATENAME(YYYY,DateValue) "year"
    ,CASE
    WHEN DATEPART(WK,DateValue)  BETWEEN '11' AND '53' THEN CONCAT(DATENAME(YYYY,DateValue),'-',DATENAME(YYYY,DateValue)+1)
    ELSE CONCAT(DATENAME(YYYY,DateValue)-1,'-',DATENAME(YYYY,DateValue))
    END AS "finyear"
FROM ##Dates;

Answer 2

As far as I know, the only way to do this would be to use a bunch of IF conditions. I have taken a shot at it and it looks like I am getting the right values. The following calculation assumes that you are looking for a Sunday to Saturday week:

Weeknum Calc = 
        IF(WEEKDAY(DATE(YEAR('Calendar'[Date]),1,1))>1 && WEEKDAY(DATE(YEAR('Calendar'[Date])-1,1,1))=1 && WEEKNUM('Calendar'[Date])=1,53,
        IF(WEEKDAY(DATE(YEAR('Calendar'[Date]),1,1))>1 && WEEKDAY(DATE(YEAR('Calendar'[Date])-1,1,1))=2 && WEEKNUM('Calendar'[Date])=1 && MOD(YEAR('Calendar'[Date])-1,4)=0,53,
        IF(WEEKDAY(DATE(YEAR('Calendar'[Date]),1,1))>1 && WEEKNUM('Calendar'[Date])=1,52,
        IF(WEEKDAY(DATE(YEAR('Calendar'[Date]),1,1))>1,WEEKNUM('Calendar'[Date])-1,
        WEEKNUM('Calendar'[Date])
        ))))

To give you further details on the IF conditions:

IF Condition 1: This is to number the first few days of a year, provided it does not start on a Sunday. If the previous year had started on a Sunday, then the first week of this year should be week 53

IF Condition 2: This is to number the first few days of a year, provided it does not start on a Sunday. This case is specific to the previous year being a leap year. If the previous year starts on a Monday and if the previous year is a leap year, then the first week of this year should be 53

IF Condition 3: This is to number the first few days of a year, provided it does not start on a Sunday. If the first week of a year does not meet conditions 1 and 2, then first week of this year should be week 52

IF Condition 4: This is to number all the other weeks of a year. If the year does not start on a Sunday, then the week number should be weeknum-1

ELSE Condition: This is to number all the other weeks of a year. If the year starts on a Sunday, then the week number should be weeknum

This should give you the desired result.