Forum Discussion

dplandry's avatar
dplandry
Regular Visitor
9 years ago

Working with dates from multiple tables

Ok, I'm new to Power BI and am really having difficulty getting this report to work.  I have 2 tables...CUSTORDR and AR_INTRASTAT.  I'm trying to build an on-time delivery report and have the main report data pulled together, but am having trouble calculating the date differences since ship_date is in AR_INTRASTAT table and the promised and requested delivery dates are in CUSTORDR table.

 

Report columns:

Cust_ID [CUSTORDR table]

SALES ORDER #  [CUSTORDR table]

CUST_PO (Text)  [CUSTORDR table]

ITEM (text)  [CUSTORDR table]

CREATE_DATE (Date)  [CUSTORDR table]

PROM_DELIVERY (Date)  [CUSTORDR table]

REQUESTED_DELIVERY (Date)  [CUSTORDR table]

TRANSIT (DAYS)  [CUSTORDR table]

SHIPMENT_NO (Number)   [AR_INTRASTAT table]

SHIP_DATE (Date)  [AR_INTRASTAT table]

 

I need the number of days between SHIP_DATE and PROM_DELIVERY in a column and SHIP_DATE and REQUESTED_DELIVERY in another column.  Any assistance would be GREATLY appreciated.

 

Thanks

Dwayne

 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    I'm not sure how you get there with the information that you have provided. How does AR_INSTRASTAT relate to CUSTORDR? What ties a customer order to a shipment?

    • dplandry's avatar
      dplandry
      Regular Visitor
      9 years ago

      Sorry I left that out of my post..  They're joined by SALESORDER# which appears in both tables.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi dplandry,

         

        Could you post your table structures with some sample data in this case? It's better to share a sample pbix file.:smileyhappy:

         

        Regards