Forum Discussion

kcantor's avatar
kcantor
Community Champion
10 years ago
Solved

How to filter distinct count

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 ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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.

a_mixed_life's avatar
a_mixed_life
Resolver I
10 years ago
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.