日期不同-Power BI

你可以试试

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Category"}, {{"data", each 
    let a=Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index", 0, 1, Int64.Type)
    in Table.AddColumn(a,"Output", each try Duration.Days([Date] - a[Date]{[Index]-1}) otherwise null)
, type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date", "Output"}, {"Date", "Output"})
in  #"Expanded data"

您可以创建 计算列 如下所示-

未测试语法(写在记事本上)。但这个逻辑应该有效。你只需要调整语法错误(如果有的话)。

output = 

var this_row_category = your_table_name[category]
var this_row_date = your_table_name[date]

var previous_date_this_category =
calculate(
    max(your_table_name[date]),
    filter(
        all(your_table_name),
        your_table_name[category] = this_row_category
            && your_table_name[date] < this_row_date
    )

)

return
if(
    not isnull(previous_date_this_category), 
    this_row_date - previous_date_this_category
)

我创建了3个DAX度量(MAXDate和PrevDate可以是最终度量中的变量):

//计算每个类别的最大日期的一个:

MAXDate =
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[Category] )
)

//计算上一日期的日期:

PrevDate = 
VAR MaxDate =
    MAX ( 'Table'[Date] )
VAR MaxPrevDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] < MaxDate
                && 'Table'[Category] = MAX ( 'Table'[Category] )
        )
    )
RETURN
    MaxPrevDate

//一种计算上述两个度量值之间的天数差的度量值:

DateDiff =
IF ( [PrevDate] <> BLANK (), DATEDIFF ( [PrevDate], [MAXDate], DAY ) )

结果: