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
Demolia
New Member

Getting data from two tables into one visual based on date

Hi All, 

I have a question for an progression dashboard I need to put the data last year in one image together with this year. 
I need to show sales and they are two different tables but both have a timestamp dd/mm/yyyy hh/mm/ss but once I put the data into the visual either one of them is showing is a dot. 

I tried to make a relationship between the to tables but that didnt work. 

What I would like is to have sales of both years in one visual and if possible without creating a whole new table with both of them combined. 

I hope the question is clear,
THanks  

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @Demolia 

Build a Dimdate table:

 

DimDATE = CALENDARAUTO()

 

Then use calculated column to get the Year and Month column.

 

Year = YEAR(DimDATE[Date])
Month = MONTH(DimDATE[Date])

 

However we can't build a relationship between Dimdate[Date] (Date Type) and Table1/Table2[timestampcreated] (Date Time Type)

You may need to build two new date columns(Date Type)  like Anand24  replied in Table1 and Table2.

3.png

Table1:

1.png

Table2:

2.png

Now build relationships between Dimdate[Date] (Date Type) and Table1/Table2[Date](Date Type)

Finally build a measure and use matrix to show the result:

 

Measure = 
SWITCH(
    TRUE(),
    MAX(DimDATE[Year])=2019,CALCULATE(SUM(Table2[Sales])),
    MAX(DimDATE[Year])=2020,CALCULATE(SUM(Table1[Sales])))

 

4.png

You can download the pbix file from this link: Getting data from two tables into one visual based on date

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

Hi @Demolia 

Build a Dimdate table:

 

DimDATE = CALENDARAUTO()

 

Then use calculated column to get the Year and Month column.

 

Year = YEAR(DimDATE[Date])
Month = MONTH(DimDATE[Date])

 

However we can't build a relationship between Dimdate[Date] (Date Type) and Table1/Table2[timestampcreated] (Date Time Type)

You may need to build two new date columns(Date Type)  like Anand24  replied in Table1 and Table2.

3.png

Table1:

1.png

Table2:

2.png

Now build relationships between Dimdate[Date] (Date Type) and Table1/Table2[Date](Date Type)

Finally build a measure and use matrix to show the result:

 

Measure = 
SWITCH(
    TRUE(),
    MAX(DimDATE[Year])=2019,CALCULATE(SUM(Table2[Sales])),
    MAX(DimDATE[Year])=2020,CALCULATE(SUM(Table1[Sales])))

 

4.png

You can download the pbix file from this link: Getting data from two tables into one visual based on date

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

amitchandak
Super User
Super User

@Demolia , Create a date table and other common dimensions if needed.

Join that with the only date in both table

 

 

Date =[Datetime].date //create a new column like

refer:https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

 

Anand24
Super User
Super User

Hi @Demolia ,

 

Can you show the structure of both the tables, your current output and expected output in pictures?

That would help to better understand the issue because as of now, a simple join between dates should be enough in the scenario you stated.

Also, what is the issue with joining you are facing?

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

The picture I would like take make is:
A represitation of sales per month 2019 vs 2020. 
         Jan     Feb 
2019  200  190 
2020  250   180  

I think you get the idea. 
Table 1 

timestampcreatedLocal store
3-4-2020 12:00A
3-4-2020 12:04A
4-4-2020 12:26A
4-4-2020 12:47A
  

Table 2

timestampcreatedLocal store
3-3-2019 15:57A
3-3-2019 16:38A
4-3-2019 12:53A
5-3-2019 15:38A


What the problem seems to be is, I pick a date format from either table but the other one doesnt seem to accept it. 
I tried to make a relationship but I either had that wrong or it didnt work at all. 
What I think I need to do is make a new Date format (Year/Month) in both tables and make a relationship with them or not? 

Hi @Demolia ,

 

I don't think you should have any issue with joining 2 date/time fields.

You aren't directly getting year and month because year and month can't be directly fetched from date/time field.

You can just convert those date/time fields into Date field and then use them to get required year:

1. Directly from Report

dtt.PNG

 

2. From Query Editor

dtt2.PNG

 

Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

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.