topic Re: making a comparison between two years for Accounts Receivable in DAX Commands and Tips

making a comparison between two years for Accounts Receivable

Anonymous — Mon, 21 Oct 2024 11:37:05 GMT

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.

Re: making a comparison between two years for Accounts Receivable

xifeng_L — Mon, 21 Oct 2024 14:37:20 GMT

Hi Anonymous

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~

Re: making a comparison between two years for Accounts Receivable

Anonymous — Tue, 22 Oct 2024 07:21:46 GMT

hi xifeng_L ,

Thank you for your response.

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

Re: making a comparison between two years for Accounts Receivable

Anonymous — Wed, 30 Oct 2024 09:37:01 GMT

Hi Anonymous ,

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.

Re: making a comparison between two years for Accounts Receivable

Anonymous — Wed, 30 Oct 2024 09:54:28 GMT

Hi Anonymous,

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.