Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
@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.
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
timestampcreated | Local store |
3-4-2020 12:00 | A |
3-4-2020 12:04 | A |
4-4-2020 12:26 | A |
4-4-2020 12:47 | A |
Table 2
timestampcreated | Local store |
3-3-2019 15:57 | A |
3-3-2019 16:38 | A |
4-3-2019 12:53 | A |
5-3-2019 15:38 | A |
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
2. From Query Editor
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |