Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kcantor
Community Champion
Community Champion

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 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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




a_mixed_life
Resolver I
Resolver I


@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.

Kris
Vvelarde
Community Champion
Community Champion

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




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.