Forum Discussion

Demolia's avatar
Demolia
New Member
6 years ago
Solved

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  

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

    Table1:

    Table2:

    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])))

     

    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. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    Table1:

    Table2:

    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])))

     

    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. 

  • Anand24's avatar
    Anand24
    Super User
    6 years ago

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

    • Demolia's avatar
      Demolia
      New Member
      6 years ago

      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? 

      • Anand24's avatar
        Anand24
        Super User
        6 years ago

        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

         

        2. From Query Editor

         

        Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.