不同表格的DAX累计总比较-Java 学习之路

如何创建DAX度量标准，以显示收据余额大于或等于付款余额的第一个收据日期？

例如，对于Jun，我希望[付款收据日期平均]度量显示为1月，因为收据余额为10，大于8的付款余额 .

对于8月份，它将显示为2月，因为这是收据余额至少为13的付款余额的第一个日期 .

Desired Result:

Output

Data Model

Data Model

表：Po收据

PoReceipt

表：付款

Payment

Attempted Solution

我已经定义了以下措施：

Receipt Balance:
=CALCULATE
(
    SUM(PoReceipt[Quantity Received]),
    FILTER
    (
        ALL ( PaymentDates ),
        PaymentDates[Payment_FullDate] <= MAX ( PaymentDates[Payment_FullDate] )
    )
)

Payment Balance:
=CALCULATE
(
    SUM(Payment[Payment Amount]),
    FILTER
    (
        ALL ( PaymentDates ),
        PaymentDates[Payment_FullDate] <= MAX ( PaymentDates[Payment_FullDate] )
    )
)

1 回答

创建计算列以获得具有净收据运行总额的第一个日期（即收据减去运行总额的支付）大于当前行上下文中的支付金额 . 计算列需要执行以下操作：

返回[PO Num]的[Payment Amount]的运行总计 . 使用EARLIER函数，以便对于外部上下文中的每一行，您对内部上下文中内部行的[Payment Date]小于或等于外部行的内部行和内部行的[Po Num]中的所有行求和等于外行的 .
根据PoReceipt表返回摘要表，该表具有扩展列"Receipt Balance Calc" . 扩展列计算PoReceipt [收到数量]的运行总计，类似于步骤1.摘要表按[Po Num]和[Receipt Date]列分组，以避免多次执行相同的计算（因为有多个由于[Receipt Num]列，具有相同[Po Num]的行 .
过滤结果仅包括收据余额大于或等于付款余额的行 .

有关计算列，请参阅DAX代码：

Payment[Payment Balance Calc]
=CALCULATE (
    SUM ( Payment[Payment Amount] ),
    FILTER (
        Payment,
        Payment[Payment Date] <= EARLIER ( Payment[Payment Date] )
        && Payment[Po Num] = EARLIER ( Payment[Po Num] )
    )
)

Payment[Payment Receipt Date]
=CALCULATE (
    MIN ( PoReceipt[Receipt Date] ),
    ALL ( PaymentDates ),
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                PoReceipt,
                PoReceipt[Po Num],
                PoReceipt[Receipt Date]
            ),
            "Receipt Balance Calc",
            CALCULATE (
                SUM ( PoReceipt[Quantity Received] ),
                FILTER (
                    PoReceipt,
                    PoReceipt[Receipt Date] <= EARLIER ( PoReceipt[Receipt Date] )
                    && PoReceipt[Po Num] = EARLIER ( PoReceipt[Po Num] )
                )
            )       
        ),
        [Receipt Balance Calc] >= EARLIER ( Payment[Payment Balance Calc] )
    )
)

最后，创建以下度量来计算收货日期的加权平均值：

Payment Receipt Date Avg:=
SUMX ( 
    Payment,
    Payment[Payment Amount] * Payment[Payment Receipt Date] 
    / SUM ( Payment[Payment Amount] )
)

回复于 2024-04-25T09:25:22+08:00

不同表格的DAX累计总比较

1 回答

相关问题