Forum Discussion

Dellis81's avatar
Dellis81
Icon for Post Prodigy rankPost Prodigy
6 years ago

DAX Calculate with filter

Hello!

 

I suspect there is a very simple solution - so embarrassed to ask.  But here goes...

 

I have a data table showing accounts receivable invoices. For every vendor with EntryType = "R",  - I want to display the "Amount" column, and then offseting pmts.   The challenge is for each InvoiceID, when a pmt is received for that invoice - that InvoiceID is displayed in the description.   For example, (in blue) an invoice was created for Vendor A (InvoiceID = 2888), and then on 1/19/19, a  ROA entry took place, with the description containing 2888.

 

The example in red, shows Vendor B with an Invoice ID 67408 - and then two partial pmts made against this same invoice.

DateAccountInvoice IDEntryTypeVendorAmountDescription
12/26/201810552888RVendorA5400 
1/19/2019105511DVendorA-54002888
10/17/20171055206RVendorB6197.31 
10/23/2017105540DVendorB-6197.31206
3/27/2017105567408RVendorB8840.93 
3/27/2017105511DVendorB-600067408
6/1/2017105532DVendorB-200067408
11/26/201910553681RVendorB5168 

 

For Vendor A, the 5400 is the correct amount with only a single invoice and ROA.

However, Vendor B - the current measure is pulling in all ROA's for Vendor B, but ignoring the InvoiceID->Description match.  For invoice 67408 the AcctsRecPaid measure should be $8,000  ($6000+$8000)

 

 

Here is my the measure I currently have - how would I create a filter to only pull in Description that equals the InvoiceID.

 

AcctsRecPaid = calculate([Amount1],Data[EntryType]<>"R",ALLEXCEPT(Data,Data[Vendor]))

 

I have shared the pbix file if it is needed.   (the data is embedded in the datatable)

https://drive.google.com/open?id=1noh56_WMKeCfl8h_g68xt9ZAvj3ghBZK

 

Thank you!

2 Replies