database - 用于计算 PowerBI 中收入损失的 DAX 代码

Question

我有一个名为mytable的表，其中包含相关列：customer_id、amount、year。

我想计算连续 2 年之间的总收入损失。

我的伪代码想法是：

1. 查找今年花费任何金额的客户
2. 查找去年花费任何金额的客户
3. 使用 1. 和 2 的列表查找去年消费但今年没有消费的客户。
4. 合计这些客户的所有金额

我尝试编写以下 DAX 代码，但最后无法计算总和，出现语法错误。

lost revenues = 
    
    VAR current_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2020))
    
    VAR old_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2019))
    
    VAR lost_customers = EXCEPT(old_customers, current_customers)
    
    VAR computed = CALCULATETABLE(VALUES('mytable'[amount]), 'mytable'[year]=2019, 'mytable'[customer_id] IN lost_customers)
    
RETURN SUM(computed[amount])

我也尝试了另一个计算值的版本，但它是错误的：

lost revenues = 
    
    VAR current_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2020))
    
    VAR old_customers = DISTINCT(CALCULATETABLE(VALUES('mytable'[customer_id]), 'mytable'[year]=2019))
    
    VAR lost_customers = EXCEPT(old_customers, current_customers)
    
RETURN SUMX(lost_customers, CALCULATE(SUM('mytable'[amount])))

我究竟做错了什么？

编辑：

这是数据的最小示例。如您所见，客户 3 在 2020 年没有花费任何金额，因此损失的收入是他在 2019 年花费的总和（197 美元）：

标签： databasepowerbidax

Answer 1

假设您表中的所有字段都是Whole Numbers，我创建了一个度量来计算LostRevenue缺失年份的 ，并分配去年作为结果。

我创建了一个表DimYear来拥有一个独特的年份列表，独立于原始TABLE.

计算：测量

LostRevenue =
VAR SelectedCustomer =
    SELECTEDVALUE ( 'Table'[customer] )
VAR SelectedYear =
    SELECTEDVALUE ( DimYear[year] )
VAR AllCustomers =
    SUMMARIZE ( 'Table', 'Table'[customer], 'Table'[year] )
VAR AllCombinations =
    CALCULATETABLE (
        CROSSJOIN ( { SelectedCustomer }, DISTINCT ( 'Table'[year] ) ),
        REMOVEFILTERS ( 'Table'[customer] )
    )
VAR MissingYears =
    EXCEPT ( AllCombinations, AllCustomers )
VAR GetYear =
    SUMMARIZE ( MissingYears, [year] )
VAR LastYearData =
    CALCULATE (
        SUM ( 'Table'[amount] ),
        FILTER ( ALLSELECTED ( DimYear ), [year] = SelectedYear - 1 )
    )
RETURN
    IF ( SelectedYear IN GetYear, LastYearData, BLANK () )

关系

已创建新表

DimYear = 
    DISTINCT('Table'[year])

输出

表参考：TABLE

顾客	数量	年
1	10	2019
2	43	2019
3	23	2019
1	10	2019
3	76	2019
2	5	2019
1	10	2019
2	10	2019
1	55	2019
1	10	2019
3	98	2019
1	15	2020
2	40	2020
1	18	2020
1	15	2020
2	7	2020
1	6	2020
2	7	2020
1	44	2020
1	15	2020