You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Power BI日期表M代码异常：最近1周/52周数据无法正常显示

avatar
云上老周
2026-6-12
问题：Power BI日期表筛选标记异常

已搭建Power BI销售仪表盘，日期表中设置了「最近1周、最近4周、最近12周、最近26周、最近52周」的销售筛选标记，数据按周聚合，且日期表与数据表已通过日期列建立关系。当前存在两个问题：

  • 版本1中最近1周和最近52周返回空白值，但最近4周、12周、26周筛选功能正常
  • 版本2中所有时间段列返回相同值

版本1 M代码（最近1周/52周空白） 

let
    // Variables
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2026, 12, 31),
    Duration = Duration.Days(Duration.From(EndDate - StartDate)),
 
    // Date Columns
    Dates = List.Dates(StartDate, Duration, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
    #"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Quarter No" = Table.AddColumn(#"Added Year", "Quarter No", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Quarter Name" = Table.AddColumn(#"Added Quarter No", "Quarter ", each "Q" & Text.From([Quarter No]), type text),
    #"Added Month No" = Table.AddColumn(#"Added Quarter Name", "Month No", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Month No", "Month Name", each Date.MonthName([Date]), type text),
    #"Added Short Month" = Table.AddColumn(#"Added Month Name", "Month", each Text.Start(Date.MonthName([Date]), 3), type text),
    #"Added Day" = Table.AddColumn(#"Added Short Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Added Week No" = Table.AddColumn(#"Added Day", "Week No", each Date.WeekOfYear([Date], Day.Monday)),
    #"Added W/C" = Table.AddColumn(#"Added Week No", "W/C", each Date.StartOfWeek([Date], Day.Monday)),
    #"Added Day Name" = Table.AddColumn(#"Added W/C", "Day Name", each Date.DayOfWeekName([Date], "en-150"), type text),
    #"Set Data Types" = Table.TransformColumnTypes(#"Added Day Name", {
        {"W/C", type date}, {"Week No", Int64.Type}, {"Day Name", type text},
        {"Date", type date}, {"Year", Int64.Type}, {"Quarter No", Int64.Type},
        {"Quarter ", type text}, {"Month No", Int64.Type}, {"Month Name", type text},
        {"Month", type text}, {"Day", Int64.Type}
    }),
 
    // Use Max Date from Weekly[WeekCommencing] with fallback
    MaxDate = try List.Max(Weekly[WeekCommencing]) otherwise Date.From(DateTime.LocalNow()),
    StartOfYear = #date(Date.Year(MaxDate), 1, 1),
    SPLYToday = Date.AddYears(MaxDate, -1),
    SPLYStartOfYear = #date(Date.Year(SPLYToday), 1, 1),
 
    // Add Filter Flag using original day-based bucket logic
    #"Add Filter Flag" = Table.AddColumn(#"Set Data Types", "Date Filter", each 
        let
            currentDate = [Date],
            daysDiffTY = Duration.Days(MaxDate - currentDate),
            daysDiffSPLY = Duration.Days(SPLYToday - currentDate)
        in
            if currentDate <= MaxDate then
                if daysDiffTY >= 1 and daysDiffTY <= 7 then "01 Week" else
                if daysDiffTY >= 8 and daysDiffTY <= 34 then "04 Weeks" else
                if daysDiffTY >= 35 and daysDiffTY <= 118 then "12 Weeks" else
                if daysDiffTY >= 119 and daysDiffTY <= 300 then "26 Weeks" else
                if daysDiffTY >= 301 and daysDiffTY <= 364 then "52 Weeks" else
                if currentDate >= StartOfYear then "YTD" else
                if daysDiffSPLY >= 365 and daysDiffSPLY <= 371 then "LY 01 Week" else
                if daysDiffSPLY >= 372 and daysDiffSPLY <= 399 then "LY 04 Weeks" else
                if daysDiffSPLY >= 400 and daysDiffSPLY <= 447 then "LY 12 Weeks" else
                if daysDiffSPLY >= 448 and daysDiffSPLY <= 545 then "LY 26 Weeks" else
                if daysDiffSPLY >= 546 and daysDiffSPLY <= 909 then "LY 52 Weeks" else null
            else if currentDate >= SPLYStartOfYear and currentDate <= SPLYToday then "SPLY - YTD"
            else null
    , type text)
in
    #"Add Filter Flag"

版本1问题原因

  1. 最近1周空白：daysDiffTY >=1 and daysDiffTY <=7的逻辑排除了MaxDate当天（差值为0），若MaxDate是周最后一天，整周日期中没有符合差值1-7的记录
  2. 最近52周空白：YTD判断在52周逻辑之后，本年范围内的日期会优先匹配YTD，导致52周标记无法触发

版本2 M代码（所有时间段值相同） 

let
    // Define date range
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2026, 12, 31),
    Duration = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, Duration, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    DateTyped = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
 
    // Add calendar attributes
    AddYear = Table.AddColumn(DateTyped, "Year", each Date.Year([Date]), Int64.Type),
    AddWeekNo = Table.AddColumn(AddYear, "Week No", each Date.WeekOfYear([Date], Day.Monday), Int64.Type),
    AddWC = Table.AddColumn(AddWeekNo, "Week Commencing", each Date.StartOfWeek([Date], Day.Monday), type date),
    AddWeekYear = Table.AddColumn(AddWC, "Week Year", each Date.Year([Week Commencing]), Int64.Type),
 
    // Define MaxDate and comparison dates
    RawMaxDate = Date.From(DateTime.LocalNow()),
    MaxDate = Date.EndOfWeek(RawMaxDate, Day.Sunday),
    SPLYToday = Date.AddYears(MaxDate, -1),
 
    // Add Date Filter (main bucket)
    AddDateFilter = Table.AddColumn(AddWeekYear, "Date Filter", each 
        let
            currentDate = [Date],
            daysDiffTY = Duration.Days(MaxDate - currentDate),
            daysDiffSPLY = Duration.Days(SPLYToday - currentDate)
        in
            if currentDate <= MaxDate then
                if daysDiffTY >= 0 and daysDiffTY <= 6 then "01 Week" else
                if daysDiffTY >= 7 and daysDiffTY <= 34 then "04 Weeks" else
                if daysDiffTY >= 35 and daysDiffTY <= 118 then "12 Weeks" else
                if daysDiffTY >= 119 and daysDiffTY <= 300 then "26 Weeks" else
                if daysDiffTY >= 301 and daysDiffTY <= 664 then "52 Weeks" else
                if daysDiffSPLY >= 365 and daysDiffSPLY <= 371 then "LY 01 Week" else
                if daysDiffSPLY >= 372 and daysDiffSPLY <= 399 then "LY 04 Weeks" else
                if daysDiffSPLY >= 400 and daysDiffSPLY <= 447 then "LY 12 Weeks" else
                if daysDiffSPLY >= 448 and daysDiffSPLY <= 545 then "LY 26 Weeks" else
                if daysDiffSPLY >= 546 and daysDiffSPLY <= 909 then "LY 52 Weeks" else null
            else null,
        type text
    )
in
    AddDateFilter

版本2问题原因

逻辑优先级错误，今年时间段与去年同期标记的范围重叠，且缺少YTD等边界判断，导致所有日期被错误匹配到同一个标记。

解决方案（修正后的M代码）

基于版本1调整逻辑优先级和差值范围，确保每个时间段标记准确触发：

let
    // Variables
    StartDate = #date(2024, 1, 1),
    EndDate = #date(2026, 12, 31),
    Duration = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
 
    // Date Columns
    Dates = List.Dates(StartDate, Duration, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table", {{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Date", type date}}),
    #"Added Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Added Quarter No" = Table.AddColumn(#"Added Year", "Quarter No", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Quarter Name" = Table.AddColumn(#"Added Quarter No", "Quarter ", each "Q" & Text.From([Quarter No]), type text),
    #"Added Month No" = Table.AddColumn(#"Added Quarter Name", "Month No", each Date.Month([Date]), Int64.Type),
    #"Added Month Name" = Table.AddColumn(#"Added Month No", "Month Name", each Date.MonthName([Date]), type text),
    #"Added Short Month" = Table.AddColumn(#"Added Month Name", "Month", each Text.Start(Date.MonthName([Date]), 3), type text),
    #"Added Day" = Table.AddColumn(#"Added Short Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Added Week No" = Table.AddColumn(#"Added Day", "Week No", each Date.WeekOfYear([Date], Day.Monday)),
    #"Added W/C" = Table.AddColumn(#"Added Week No", "W/C", each Date.StartOfWeek([Date], Day.Monday)),
    #"Added Day Name" = Table.AddColumn(#"Added W/C", "Day Name", each Date.DayOfWeekName([Date], "en-150"), type text),
    #"Set Data Types" = Table.TransformColumnTypes(#"Added Day Name", {
        {"W/C", type date}, {"Week No", Int64.Type}, {"Day Name", type text},
        {"Date", type date}, {"Year", Int64.Type}, {"Quarter No", Int64.Type},
        {"Quarter ", type text}, {"Month No", Int64.Type}, {"Month Name", type text},
        {"Month", type text}, {"Day", Int64.Type}
    }),
 
    // Use Max Date from Weekly[WeekCommencing] with fallback
    MaxDate = try List.Max(Weekly[WeekCommencing]) otherwise Date.From(DateTime.LocalNow()),
    MaxDateEndOfWeek = Date.EndOfWeek(MaxDate, Day.Sunday),
    StartOfYear = #date(Date.Year(MaxDateEndOfWeek), 1, 1),
    SPLYToday = Date.AddYears(MaxDateEndOfWeek, -1),
    SPLYStartOfYear = #date(Date.Year(SPLYToday), 1, 1),
 
    // Add Filter Flag with corrected logic
    #"Add Filter Flag" = Table.AddColumn(#"Set Data Types", "Date Filter", each 
        let
            currentDate = [Date],
            daysDiffTY = Duration.Days(MaxDateEndOfWeek - currentDate),
            daysDiffSPLY = Duration.Days(SPLYToday - currentDate)
        in
            // 优先判断去年同期标记
            if daysDiffSPLY >= 365 and daysDiffSPLY <= 371 then "LY 01 Week" else
            if daysDiffSPLY >= 372 and daysDiffSPLY <= 399 then "LY 04 Weeks" else
            if daysDiffSPLY >= 400 and daysDiffSPLY <= 447 then "LY 12 Weeks" else
            if daysDiffSPLY >= 448 and daysDiffSPLY <= 545 then "LY 26 Weeks" else
            if daysDiffSPLY >= 546 and daysDiffSPLY <= 909 then "LY 52 Weeks" else
            // 再判断今年的时间段标记
            if currentDate <= MaxDateEndOfWeek then
                if daysDiffTY >= 0 and daysDiffTY <= 6 then "01 Week" else
                if daysDiffTY >= 7 and daysDiffTY <= 34 then "04 Weeks" else
                if daysDiffTY >= 35 and daysDiffTY <= 118 then "12 Weeks" else
                if daysDiffTY >= 119 and daysDiffTY <= 300 then "26 Weeks" else
                if daysDiffTY >= 301 and daysDiffTY <= 364 then "52 Weeks" else
                if currentDate >= StartOfYear then "YTD" else null
            else if currentDate >= SPLYStartOfYear and currentDate <= SPLYToday then "SPLY - YTD"
            else null
    , type text)
in
    #"Add Filter Flag"

关键修正点

  1. 修复最近1周空白：将差值范围改为daysDiffTY >=0 and daysDiffTY <=6，包含周最后一天，确保整周日期被匹配
  2. 修复最近52周空白：调整逻辑顺序，将YTD判断放在所有时间段之后，避免覆盖52周标记
  3. 统一周边界：将MaxDate转换为周最后一天，确保按周聚合的时间段范围准确
  4. 修复日期范围缺失：调整Duration计算，避免遗漏日期表最后一天

内容的提问来源于stack exchange，提问作者Wasif Mir

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.12 23:45:53