DAX 模型发票运行余额

问题描述

我有一张发票表和一张付款表.发票表由发票编号、金额、发票日期、到期日组成.付款表由发票编号、金额、付款日期组成.发票表与到期日列上的日期表具有活动关系.付款表与发票 ID 列上的发票表具有活动关系.

I have a table with invoices and a table with payments. The Invoice table consists of invoice id, amount, invoice date, expiry date. The payment table consists of invoice id, amount, payment date. The invoice table have an active relationship with the date table on the expiry date column. The payment table have an active relationship with the invoice table on the invoice id columns.

我希望能够显示任意一天的发票余额.即，如果我在特定日期过滤报告或页面，我希望查看每张发票当天的实际余额.任何人都知道如何在不创建新表的情况下完成此操作并以编程方式在其中填写每日发票余额条目?

I would like to be able to show the invoice balance on an arbitrary day. Ie if I filter the report or page on a particular date I'd like to see the acual balance on that day per invoice. Anyone know how to acomplish this without creating a new table and programmatically fill it with invoice balance per day entries?

推荐答案

给你:

InvoiceTotalAmount:=
CALCULATE(
    SUM(Invoice[Amount])
    ,ALL(DimDate) // The active relationship between Invoice[ExpiryDate]
                  // and DimDate[Date] would cause this to only be valid
                  // on the expiry date - we don't want that.
)

PaymentTotalToDate:=
CALCULATE(
    CALCULATE( // We'll manipulate the relationship in the inner
               // CALCULATE() before modifying context based on it
        SUM(Payment[Amount])
        ,USERELATIONSHIP(Payment[Date], DimDate[Date])
   )
    ,FILTER( // Now that that we're looking at the right relationship to
             // DimDate, we can alter the date range in context
        ALL(DimDate)
        ,DimDate[Date] <= MAX(DimDate[Date])
            // Here, we take all dates less than the latest date in
            // context in the pivot table - current date if 1 date in
            // context, else last of week, month, quarter, etc....
    )
)

InvoiceBalanceToDate:=[InvoiceTotalAmount] - [PaymentTotalToDate]

如果您没有使用 Invoice[ExpiryDate] 和 DimDate[Date] 之间的活跃关系，我会将其标记为非活跃关系，并将 Payment[Date] 和 DimDate[Date] 之间的关系标记为活跃关系.然后，您可以省去 [InvoiceTotalAmount] 中的 CALCULATE() 和 ALL() 以及 [PaymentTotalToDate] 中的内部 CALCULATE().

If you're not utilizing that active relationship between Invoice[ExpiryDate] and DimDate[Date], I'd mark it as inactive and the relationship between Payment[Date] and DimDate[Date] as the active one. You could then dispense with the CALCULATE() and ALL() in [InvoiceTotalAmount] and the inner CALCULATE() in [PaymentTotalToDate].

我的模型图:

这篇关于DAX 模型发票运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！