cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Getting data from two tables into one visual based on date

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
Highlighted
Solution Sage
Solution Sage

Re: Getting data from two tables into one visual based on date

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

Highlighted
New Member

Re: Getting data from two tables into one visual based on date

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? 

Highlighted
Super User IX
Super User IX

Re: Getting data from two tables into one visual based on date

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Solution Sage
Solution Sage

Re: Getting data from two tables into one visual based on date

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 !!!
Highlighted
Microsoft
Microsoft

Re: Getting data from two tables into one visual based on date

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors