Power BI Weeknum迭代

Question

我的目标是基于修改后的4-4-5日历，使用DAX或m代码显示weeknum，如果它更有意义的话。

这意味着一月份的第一个星期天是第1周，所有的周都应该是完整的周(7天)。2018年年底是第53周，根据定义，它将延长到2019年1 /5周六-2019年的第一个周日将是2019年1/6，也就是第一周。我每年都无法重置weeknum函数。如果我一年中的前几个日历天是52周或53周，那么下一周应该是1，而不是从第2周开始。

尝试通过if语句、lookupvalue等减去-1 weeknum总是会抛出一些东西。

使用WEEKNUM(DATE,1)将从周日开始提供完整的周，除非是年初。

使用WEEKNUM(DATE,17)还将从周日开始提供完整的周，如果不是完整的7天，则还会延长前一年结束的一周。(第53周延至下一年的第1天历周)。

问题仍然是，进入明年，第一周要么部分或完全被前一年年底的一周取代。基本上，我只能获得几乎一年的正确结果，而不是日历表中的5+年份。

我还有周开始日期列、周结束日期列和年日列(1到365/366)。关于每年迭代适当的weeknum的逻辑有什么建议吗？谢谢。

Answer 1

据我所知，要做到这一点，唯一的方法是使用一堆IF条件。我已经尝试过了，看起来我得到了正确的值。以下计算假设您正在寻找从周日到周六的一周：

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

要进一步详细介绍IF条件，请执行以下操作：

如果条件1:这是对一年的前几天进行编号，前提是它不是从星期天开始的。如果前一年是从星期天开始的，那么今年的第一周应该是第53周。

如果条件2:这是对一年的前几天进行编号，前提是它不是从星期天开始的。这种情况特定于前一年是闰年。如果前一年从星期一开始，并且前一年是闰年，则今年的第一周应该是53

如果条件3:这是对一年的前几天进行编号，前提是它不是从星期天开始的。如果一年的第一周不满足条件1和2，则今年的第一周应为52周。

如果条件4:这是一年中所有其他周的数字。如果一年不是从星期日开始的，则周数应为weeknum-1

ELSE条件:这是一年中所有其他周的数字。如果一年在星期天开始，则周数应为weeknum

这应该会给你想要的结果。

Answer 2

我有类似的财务周数，期间和日期的要求。

我在数据库中创建了表，并已将其导入到poweri bi报告中。

我的财务周从3月2日星期天开始，从今年开始10年的财务周号。

下面的脚本可能会对你有所帮助：

-- 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;