Difference between two rows if there is one column the same

[Anonymous]

Hi all,

I'm trying to create an overview from our accounting system with the outstanding items of our debtors.
For this we have a view that has already collected all data.

So now I actually have a pretty simple overview in which I see a line of the invoice and whether a paid amount is attached to it.
The problem, however, is with the transition of the year.

Throughout the year, everything is entered in the 'Purchase' diary. But at the beginning of the year, an opening balance sheet is drawn up (to include last year's outstanding items).
As a result, I now see one invoice number twice in my view.

Row A:
Invoice number: 123456789
Date: 31-12-2020
Amount: 1000, -
Payed: 0, -
Open: 1000, -
Diary: Purchase

Row B:
Invoice number: 123456789
Date: 31-12-2020
Amount: 1000, -
Payed: 1000, -
Open: 0
Diary: Opening balance

So the invoice has been payed, but the line from the 'Purchase' diary will still be open.

Is there a smart way, perhaps with DAX, to let PowerBI search for the same outstanding amount on journal 'sales' unless another line exists in journal 'start balance'.

Or maybe someone has a completely different idea?

Thanks in advance for any suggestions!

[Anonymous]

Hi @Anonymous 

Does RowA and RowB two rows in the same table?

My Sample:

Measure = 
VAR _Count = COUNTAX(FILTER(ALL('Table'),'Table'[Invoice number] = MAX('Table'[Invoice number])),'Table'[Invoice number])
Return
CALCULATE(DISTINCTCOUNT('Table'[Invoice number]),FILTER(ALL('Table'),_Count>=2&& 'Table'[Date] = DATE(2020,12,31)&&'Table'[Diary]<>"start balance"))

Result :

If this reply still couldn't help you solve your problem, please share a sample with me.

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @Anonymous ,

First off: thank you for your reply!

These rows are indeed in the same Table.

So I want to create a overview with all of the 'open invoices' (amount, not count).

But i'm getting an error when i'm using your Measure.

I do think that i made a mistake in replacing some of the tables.

Here is a short example of the database:

OspFactuurNummer ; invoice number

OspFactuurBedrag: invoice amount

OspBetaaldBedrag: payed amount

OspOpenstaand: open amount

So the result I would like to see that is 

Invoice:

1703562 - payed (not showing in chart)

1703563 - open (showing in chart)

1703564 - open (showing in chart)

1703565 - open (showing in chart)

I can't add a pbix or xlsx file, how can i share a example? Or just images:

[Anonymous · Accepted solution]

Hi  

Please try this measure.

Filter = 
VAR _FatNum =CALCULATETABLE( VALUES('Table'[OspFactuurNummer]),FILTER('Table','Table'[OspOpenstaand] = 0))
Return
IF(MAX('Table'[OspFactuurNummer]) in _FatNum,0,1)

Build a matrix as your screenshot , add this measure into filter field and set this measure to show items when value = 1.

Result is as below.

Best Regards,

Rico Zhou

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