locked
Matching order and receipt dates in separate tables RRS feed

  • General discussion

  • I have two fact tables, one for orders and one for receipts.  The tables do not have a relationship because the purchase order (PO)numbers they key from have many:many.  One PO has multiple line numbers, and each line number can have multiple items received.

    For example,

    In the order table PO #123 has line item #2 with a quantity of 5 widgets ordered on July 1.  A calculated due date is July 3

    In the receipt table PO #123 and line item #2 has 2 items received on July 2, 2 on July 5, and 1 on July 6.

    I would like to define a measure that tells me by PO, by line (or average across lines if I want to look at the whole PO)

    - how many items and what % were delivered within 1 day

    - what was the max # of days for all the items in the line to have been delivered (e.g. MAX delivery date for the lines)

    - what quantity and % of items were delivered past the due date

    As a newbie I don't know if i should look to dynamically create a lookup table of unique PO #'s and lines or if there's something I can do with DAX and RELATED() or similar functions.  Thanks for any help!

    Friday, July 17, 2015 7:19 PM

All replies

  • Can you use Power Query to create a Merge query to get all of the data per PO# and Line # on a single row? Because if you do that, everything you want to do is trivial.
    Friday, July 17, 2015 8:48 PM
  • Can you post a sample of your data rows, I'm having trouble visualizing what your data looks like, especially for the receipt table, are there multiple rows for each receipt or are the two receipts in 4 columns?
    Saturday, July 18, 2015 12:08 AM
  • Each receipt quantity has its own row.  So in the example above there would be 3 rows, one for each receipt event.  The dataset is rather large and has a lot of columns at the moment - who received the quantity, at what location, GLN, the price it was received at, etc.  (Yes, we'd like to take advantage of them in the future so we don't want to leave them off.)

    Order Table

    PO      Row  Qty  Date   Due Date

    123     2      5    July 1   July 3

    Receipt Table

    PO      Row  Qty  Date    Received At     Received By   ....

    123     2      2     July 2

    123     2      2     July 5

    123     2      1     July 6

    Tables are generated from queries against our PeopleSoft ERP.  Orders, Receipts, and Invoice data are in separate modules.  (Yes, eventually we'll want to link payment information too)  If there's a way to keep the tables separate in PowerPivot we'd prefer that since the individual queries out of PSFT are simple to maintain and having the data model reflect the PSFT queries helps in debugging. We have some staff movement in both our IT and Supply Chain Analytics group and complex things tend to get lost with turnover.

    Saturday, July 18, 2015 3:17 PM
  • OK, I have some sample data created and will work on this.

    BTW, either route you go, you will need a table of just distinct PO #'s in order to wire everything together properly and maybe a table of distinct row numbers, haven't got that far.

    In the mean time, I really suggest you give this a try because it makes all of the so much easier. Here is the Power Query to combine both of your tables into one table.  Basically, you could use your existing exports out of PeopleSoft, not sure what you are using for that but if it is Power Query, then you just create a third "Merge" query to combine them and match on both PO and Row, works like a champ:

    let
        Source = Table.NestedJoin(orders,{"PO", "Row"},receipts,{"PO", "Row"},"Receipts"),
        #"Expanded Receipts" = Table.ExpandTableColumn(Source, "Receipts", {"Qty", "Date"}, {"Receipts.Qty", "Receipts.Date"})
    in
        #"Expanded Receipts"

    You end up with

    PO Row Qty Date Due Date Receipts.Qty Reciepts.Date

    123 1 2 7/1/2015 12:00:00 AM 7/5/2015 1 7/4/2015
    123 1 2 7/1/2015 12:00:00 AM 7/5/2015 1 7/6/2015
    123 2 5 7/1/2015 12:00:00 AM 7/3/2015 2 7/2/2015
    123 2 5 7/1/2015 12:00:00 AM 7/3/2015 2 7/5/2015
    123 2 5 7/1/2015 12:00:00 AM 7/3/2015 1 7/6/2015
    124 1 2 7/10/2015 12:00:00 AM 7/31/2015 2 7/30/2015

    In any case, I'll work on keeping them in separate tables and see what can be done.


    Saturday, July 18, 2015 4:17 PM
  • To give you some idea of how trivial this is when you merge it all, you can create calculated columns like so:

    • Days =[Receipts.Date]-[Date]
    • Late =IF([Receipts.Date] > [Due Date],[Qty],0)
    • One Day =IF([Receipts.Date] = [Due Date]+1,[Qty],0)

    And then Calculated Fields:

    • TotalQuantity:=SUM([Qty])
    • TotalDays:=SUM([Days])
    • TotalLate:=SUM([Late])
    • TotalOneDay:=SUM([One Day])
    • PercentLate:=[TotalLate]/[TotalQuantity]
    • PercentOneDay:=[TotalOneDay]/[TotalQuantity]

    Pretty much there at that point with a matrix or table and the right fields.

    Saturday, July 18, 2015 4:47 PM
  • Thanks so much.  I'll look into merging the queries.  But... there are so many excellent reasons to keep Order and Receiving files separate outside of this analysis that it's a tough call.  There are a lot of things I'd like to do in the future that merging them in one area might lock me in.

    As a newbie, learning DAX is hard.  I've invested in books like Rob Collie's and DAX patterns but there's still a "gap" between what i know and what they're saying.  Which table should a measure go in?  What filters do CALCULATE operate against?  Do slicers operate against just one created pivot table or against anything you create including CUBEMEMBER functions?  I've been pretty good (awesome according to my cohorts) in Excel, particularly with array formulas, but as soon as I discovered DAX a month ago i realized i was missing HUGE opportunities.  But it's more of a jump than i realized.

    So... if there are answers to my questions that allow the tables to remain separate, even if not optimal, I'd like to understand those too.  What's a good way to get my head around the whole enchilada?

    Sunday, July 19, 2015 10:10 PM
  • So, you can have your separate tables as well as the merged table and use them for different things. In fact, you kind of have to do it that way really.

    The issue with what you are attempting to do is that your POOn  table has multiple lines with the same PO# and multiple lines with the same Row #. This means that you cannot relate one to the other easily and if you add a third table with PO#, then Power Pivot isn't very smart figuring out the transitive relationships because you also need a unique Row table. I've had better success with Power BI Preview in this regard. Basically, a row in receipts relates to a specific row in the orders table only when 2 conditions are true and that's just not something that I have found DAX capable of doing. Actually, I don't think it is DAX as much as the modeling "smarts" in identifying transitive relationships.  Regardless, on top of all this, you want to make comparisons between these tables but in my experience the filter functions in DAX in Power Pivot only allow you to have a single column specified. So, you can say things like receipts[Qty] > 2 but not something like receipts[Qty] > orders[Qty].

    It seems that you might be able to get around this problem by essentially taking a page out of some of the tricks that people have used to overcome the lack of many-to-many relationship support in Power Pivot. Have a look at these articles and see if they help but the bottom line is that what you are trying to do has a very straight forward approach to solve it, but not in keeping the tables separate due to the current limitations in Power Pivot model behavior. The last one may be the most suitable, it's tough to say.

    http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

    http://www.bp-msbi.com/2011/02/many-to-many-relationship-modelling-in-powerpivot-workaround/

    http://social.technet.microsoft.com/wiki/contents/articles/22202.a-practical-example-of-how-to-handle-simple-many-to-many-relationships-in-power-pivotssas-tabular-models.aspx

    http://www.powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/

    Sunday, July 19, 2015 11:18 PM
  • I seem to recall seeing an article recently showing how to dynamically extract unique information for such a lookup table.  Not creating the full merge, just extracting the DISTINCT values - in this case PO # and Line #.  Can't seem to Google it now though.

    I'm not sure about performance if I essentially duplicated the PO and Receipt tables into a merged entity - twice the data and half the fun!

    Monday, July 20, 2015 2:11 PM
  • I've put upwards of 30 million rows into Power Pivot and been OK in terms of performance, etc.

    Monday, July 20, 2015 3:09 PM
  • Hang on, I think I have been missing something easy. Create a new column in both of your import queries or as a calculated column that combines PO # and Row, a simple concatenation of both of those fields or with some additional characters so that you end up with something like "123_1". Then, you can relate the two tables to one another using a single field. Then, something like this pattern should work:

    http://www.daxpatterns.com/new-and-returning-customers/

    Tuesday, July 21, 2015 11:26 AM