DAX模型发票结余 [英] DAX model invoice running balance

问题描述

我有一张有发票的桌子和一张有付款的桌子。发票表由发票编号，金额，发票日期，有效期组成。付款表包含发票编号，金额，付款日期。发票表与到期日期列上的日期表具有活动关系。付款表与发票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].

我的模型图：

My model diagram:

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