cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CloudMonkey Member
Member

2 linked tables with different reference dates, need to present on same axis

Hi,

 

I have 2 tables, Orders and Deliveries. I need to create a table that lists the number of orders and deliveries by month. I’m confused because I can only use 1 column as an axis but I have 2 date columns (one in the Orders table and one in the Deliveries table). The tables are linked but I can’t just use the Order date as the axis because this will just list all deliveries by their order date (not their delivery date).

 

Please can you tell me how to create a table as per the last screenshot below?

 

Orders table:

orders 0926am.JPG

 

Deliveries table:

deliveries 0926am.JPG

 

Relationships (I tried to create a common calendar but I can't make both date relationships active)

relationships 0926am.JPG

 

Current output (incorrect because deliveries are being mapped by the order date):

visualisation (correct) 0952am.JPG

 

 

Target output (orders mapped by order date, deliveries mapped by delivery date - and both on the same month axis). Please can you tell me how to create this table in Power BI?

visualisation (correct) 0926am.JPG

 

Thank you for your help!

 

CM

9 REPLIES 9
vaibhavdesai Established Member
Established Member

Re: 2 linked tables with different reference dates, need to present on same axis

Hi,

Here's what you need to do:th

1. Create a Calendar dimension and join the table Orders and Delivery to it.

2. Remove the relationship between Orders and Delivery table.

result.PNG

3. Create 2 measures as below:

Deliveries = COUNTROWS( Delivery )
Orders = COUNTROWS( Orders )

4. Create a matrix visual and drag these 2 measures against dates:

result.PNG

Thanks.

PradipMCT Regular Visitor
Regular Visitor

Re: 2 linked tables with different reference dates, need to present on same axis

Hi @CloudMonkey 

 

Your relationship could be a problem. 

 

Connect Orders table to Calendar table and Delivery table to Calendar table. No Relationship needed between Orders table and Deliveries table.

 

Once the relationship is complete, now Pull the Date from Calendars table, and the respective fields from Orders and Delivery table can be dragged to this table. 

 

Hope this solves your problem!

 

If I have answered your question then please do mark this as a solution.

 

Regards,

Pradip Microsoft Certified Trainer

Pradip's YouTube Channel

CloudMonkey Member
Member

Re: 2 linked tables with different reference dates, need to present on same axis

Thanks guys but I think I need to keep the link between Orders and Deliveries because that database structure will be needed later on for other analysis. I tried the below approach, what do you think? Is it good database structure (I'm not very experienced so am concerned about making mistakes that will make life harder later!)?

 

screenshot 3.JPG

 

screenshot 2.JPG

 

Many thanks for your help!

 

CM

PradipMCT Regular Visitor
Regular Visitor

Re: 2 linked tables with different reference dates, need to present on same axis

Hi @CloudMonkey ,

 

The solution given by @vaibhavdesai and I are the ideal way to link the data base to the calendar table. It will be more helpful to use time intelligence functions here.

 

You can get the report that you want in the relationship solutions given earlier. What you have used wont be a good data base architecture.

 

Regards,

Pradip Microsoft Certified Trainer

Pradip's YouTube Channel

vaibhavdesai Established Member
Established Member

Re: 2 linked tables with different reference dates, need to present on same axis

Hi @CloudMonkey,

Ideally, there should not be any relationships between your fact tables (Orders and Delivery in your scenario).

The data model should ideally be a Star schema or snow flake schema.

Thanks.

Super User
Super User

Re: 2 linked tables with different reference dates, need to present on same axis

@CloudMonkey 

One thing you could do is move the delivery date to the orders table.  Then  you link both date fields to your dates table and invoke the second relationship when needed.  In the image blow the inactive (dotted line) relationship is between 'Dates'[Date] and 'Orders'[DeliveryDate]

DeliveryDate.jpg

Then a couple of example measures to show how the relationships are used.

Amount = SUM ( Orders[OrderAmount] )
Amount by Delivery = 
CALCULATE(
    [Amount],
    USERELATIONSHIP( Orders[DeliveryDate], Dates[Date] )
)

AmountByDelivery.jpg

I have attached my sample file for you to look at.

smpa01 Senior Member
Senior Member

Re: 2 linked tables with different reference dates, need to present on same axis

@CloudMonkeyI may have what you need.

 

From Orders_table and Deliveries Table I created a following table called

DateTbl
DateTbl = 
VAR Summarize_Delivery = SELECTCOLUMNS(Deliveries,"Date",Deliveries[DeliveryDate])
VAR Summarize_Orders = SELECTCOLUMNS(Orders,"Date",Orders[OrderDate])
VAR Union_All=Union(Summarize_Delivery,Summarize_Orders)
RETURN
Union_All
create Calulated Columns as following
Delivery_ID = LOOKUPVALUE(Deliveries[DeliveryID],Deliveries[DeliveryDate],DateTbl[Date])
Order_ID = LOOKUPVALUE(Orders[DeliveryID],Orders[OrderDate],DateTbl[Date])
create Measures as following
Delivery_Count = COUNT(DateTbl[Delivery_ID])
Order_Count = COUNT(DateTbl[Order_ID])
resulting table as following
cx.PNG

Now when you plot

xc.PNG

 

 

Super User
Super User

Re: 2 linked tables with different reference dates, need to present on same axis

Hi,

There should not be a relationship between the Order and Deliveries Table.  There should just be 2 active relatinships - From Date column of Orders Table to the Date column of the Calendar Table and from the Date column of the Deliveries Table to the Date column of the Calendar Table.  To your visual, drag dates from the Calendar Table and write these measures:

Measue1 = COUNTA('Orders'[Delivery ID])

Measure2 = COUNTA(Deliveries'[Delivery ID])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
Community Support Team

Re: 2 linked tables with different reference dates, need to present on same axis

Hi @CloudMonkey 

 

If you've fixed the issue by your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. thanks!

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 137 members 1,720 guests
Please welcome our newest community members: