making a comparison between two years for Accounts Receivable

[Mikevoc]

Hello,

I am trying to make a table which displays how many invoices are still open at the end of each month in PowerBI. I got the task to create a visualisation based on from an ERP software my company is using since they want more control over the visualisation. see following picture:

The diagram shows how much is still open at the end of the month Jan, Feb, Mar, and so on during 2 different years.

I have created a table that includes Debtor code and name, Invoice date, Payment date, and amount.

Then I created a matrix based on the data available:

With the following formula:

Sum Debits =

SUM('DebtorsInvoices’[Amount])

Which displays the total value correctly. For example I take code 100034 and on January. When I try doing it in excel, I see these values amounted to 95.945:

However, what I want is the snapshot amount of open invoices during a specific year. For example, I want to see the amount of unpaid invoices during January 2023. In Excel, I would custom filter the invoice date to before 01/02/2023 and payment date either greater than 31/01/2023 or blank which will filter the invoice like this:

Which equals to  20.282. How can I achieve this in PowerBI? I tried using the formula:

OpenInvoicesByMonth =

VAR _EndOfCurrentMonth = MAX('DateTable'[Date])

RETURN

CALCULATE(

    SUM('DebtorsInvoices’[Amount]),

    FILTER(

        'Debiteuren mutaties',

        [Invoice Date] <= _EndOfCurrentMonth &&

        (ISBLANK([PaymentDate]) || [PaymentDate] > _EndOfCurrentMonth)

    )

)

But, that formula displays the result only for the following invoices:

How can I include the paid invoices as well?

Thank you for your assistance, and please bear with me as I am still new to PowerBI.

[v-jiewu-msft]

Hi @Mikevoc ,

Based on the description, drag the Date table date column to slicer visual and select the date. Then, using the following dax formula.

OpenInvoicesByMonth = 
VAR _EndOfCurrentMonth = MAX('DateTable'[Date]) 
RETURN 
CALCULATE( SUM('DebtorsInvoices'[Amount]), 'DebtorsInvoices'[InvoiceDate] <= _EndOfCurrentMonth && ( ISBLANK('DebtorsInvoices'[PaymentDate]) || 'DebtorsInvoices'[PaymentDate] > _EndOfCurrentMonth ) )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mikevoc]

Hi @v-jiewu-msft,

Thank you for your reply. 

your formula returns an error:

The value for Invoice Date cannot be determined. Either the column doesn't exist, or there is no current row for this column.

the same goes for PaymentDate.

[xifeng_L]

Hi @Mikevoc 

You can try the following measure.

OpenInvoicesByMonth =

VAR _EndOfCurrentMonth = MAX('DateTable'[Date])

RETURN

CALCULATE(

    SUM('DebtorsInvoices’[Amount]),

    FILTER(

        ALL('Debiteuren mutaties'),

        [Invoice Date] <= _EndOfCurrentMonth &&

        (ISBLANK([PaymentDate]) || [PaymentDate] > _EndOfCurrentMonth) &&
        
        'Debiteuren mutaties'[Code] = MAX('Debiteuren mutaties'[Code])

    )

)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Mikevoc]

hi @xifeng_L ,

Thank you for your response.

I tried using the formula you provided but it returns me this: