Calculating amount by voucher with cut off date not working properly

[Sylvain74]

Dear All,

I have a fact table called factGLDetails linked to few dimension tables dimLedgerDate, dimAccount, dimVoucher.

The fact table represents a ledger details where each line can be a voucher or a payment.  When this a payment I know to which voucher(s) it is applied. My objective is to calculate the Voucher Amount (only lines which are of type vouchers, excluding lines of type payment), calculate the Paid Amount (only line of type payment) and calculating an open amount. All these 3 measures should be calcualted by voucher.

To do so I wrote the below DAX expression:

Here below is an extract of the Journal Ledger:

For example we see that for voucher DM-315, the Voucher amount is 183.09, the payment PAY-582 has been made for 183.09 and therefore the Open Amount should be 0.

But when I display a matrix showing that, I don't have the expected result:

For DM-315, it's OK but PowerBI shows values for account in CHF where does not exists for the Source Entity (Counterparty), on top of that I see some Vouchers which does not belong to the Source Entity...

I'm sure it's related to the DAX expression I wrote but I cannot figure what is incorrect?

Can you please help me ?

Thanks.

[Sylvain74]

Any help would be very welcome 🙂

[ahadkarimi]

Hi @Sylvain74, try two measures below, and if you encounter any issues, let me know.

AMOUNT = 
var VoucherAmt = 
SUMX(
    CALCULATETABLE(
        '(FACT) GL Details',
        '(FACT) GL Details'[SourcePurchaseVoucherDimKey] > 0 || '(FACT) GL Details'[SourceSaleInvoiceDimKey] > 0
    ),
    '(FACT) GL Details'[Movement FRGN]
)

RETURN IF(ISBLANK(VoucherAmt), 0, VoucherAmt)

PAID = 
var VoucherPmt = 
SUMX(
    CALCULATETABLE(
        '(FACT) GL Details',
        '(FACT) GL Details'[SourceAPPaymentDimKey] > 0 || '(FACT) GL Details'[SourceARPaymentDimKey] > 0
    ),
    '(FACT) GL Details'[Movement FRGN]
)

RETURN IF(ISBLANK(VoucherPmt), 0, VoucherPmt)

Did I answer your question?  If so, please mark my post as the solution!✔️
Your Kudos are much appreciated!  Proud to be a Responsive Resident!

[Sylvain74]

Hi ,

Unfortunately it does not work...

In my data model I have a dimesion for the account (Full Description) and for the Voucher (Voucher No).

However, I don't have dimension for Currency and Source Entity which come directly from the factTable.

Could it be a problem?

[Sylvain74]

Hi @ahadkarimi ,

I tried this solution and it did not work....

As you can see below it shows records for CHF whereas this Source Entity does not have any records in CHF and it shows all the different possible accounts under USD whereas only few of them have been used.

And the value for those lines are 0....

Here below is the result:

In my data model I have a dimesion for the account (Full Description) and for the Voucher (Voucher No).

However, I don't have dimension for Currency and Source Entity which come directly from the factTable.

Could it be a problem?

[Sylvain74]

I wrote below measures which are working a bit better. However as soon as I add COALESCE to replace Blank value by zero, it shows a lot of other invoices not related to the current customer:

AMOUNT =
var VoucherAmt =
SUMX(
CALCULATETABLE(
'(FACT) GL Details',
('(FACT) GL Details'[SourcePurchaseVoucherDimKey] > 0 || '(FACT) GL Details'[SourceSaleInvoiceDimKey] > 0)
),
'(FACT) GL Details'[Movement FRGN]
)

RETURN VoucherAmt

and

PAID =
var VoucherPmt =
SUMX(
CALCULATETABLE(
'(FACT) GL Details',
('(FACT) GL Details'[SourceAPPaymentDimKey] > 0 || '(FACT) GL Details'[SourceARPaymentDimKey] > 0)
),
'(FACT) GL Details'[Movement FRGN]
)

RETURN VoucherPmt

Here below is the results whithout handling blank values...and it looks Ok...

But as soon as I try to replace blank values by 0, it gives me below result:

I don't understand what I am missing... 😞