How to filter distinct count

[kcantor]

@Greg_Deckler and @MattAllington

You are the first to come to mind on this question. It should be fairly simple for those of your ability levels . . .

I have a sales order number on my table with an order date and a posting date. A sales order can have multiple invoices attached to it but an invoice can only attach to a single sales order. I need to create a calculation to determine how many sales orders for the month have all associated invoices post on the same day as the order was placed. The part I am not sure about is how to make sure invoices associated with that sales order do not post on other days.

Any suggestions?

ANd, just for fun, my table lines are for items on the sales order so a single sales order can have multiple lines on the table.

[Greg_Deckler · Accepted solution]

If I understand the problem correctly, you could do a rather brute force and unelegant way.

Have a separate table for your Sales Orders which is all unique values. Relate your Invoices table to your Sales Orders table. In your Sales Orders table, create a column that is essentially (psuedo-code):

Number of Invoices = COUNTROWS(RELATED([Invoices]))

You could also do this as a measure.

Now, create another column that is:

Number of Same Day Invoices = CALCULATE(COUNTROWS(RELATED([Invoices])),[Order Date] = [Invoice Date])

This could also be a measure that instead of repeating the formula just referenced your previous measure.

Create a final column/measure like:

All Same Day = IF([Number of Invoices] = [Number of Same Day Invoices], "Y", "N")

Now you should be able to easily determine how many Sales Orders had all of their associated Invoices post on the same day.

[kcantor]

Elegance is over rated. If you need to hammer in a finish nail and all you have is a framing hammer, make it work.

Thanks a bunch @Greg_Deckler. You always have the best, simple answers for me.

[a_mixed_life]

---

@kcantor wrote:

I need to create a calculation to determine how many sales orders for the month have all associated invoices post on the same day as the order was placed. The part I am not sure about is how to make sure invoices associated with that sales order do not post on other days.

Any suggestions?

ANd, just for fun, my table lines are for items on the sales order so a single sales order can have multiple lines on the table.

---

If the 'posted' invoices are posted against the Sales Order, it should record the posting date and those shouldn't change as this would show on "Posted Sales Invoice" table (We're using Dynamics NAV). If they are to create a new invoice on a different day against the sales order, it would generate a new invoice number as well as new posting date. Unless your ERP doesn't work that way.

[Vvelarde]

Do you have 1 table with this structure:

Sale Order  Invoice  Item OrderDate  PostingDate

SO001        INV1     1      01/01/2016 01/01/2016

SO001        INV1     2       02/01/2016 03/01/2016

SO001        INV1     3      01/01/2016 01/01/2016

SO002        INV2     1       01/01/2016 01/01/2016

SO002        INV3     1        01/01/2016 01/01/2016

SO003        INV4     1        01/01/2016 01/01/2016

SO003        INV4     2       01/01/2016 01/01/2016

SO003        INV5     1        01/01/2016 02/01/2016

Or have 2 tables Sale Orders and Invoices