Trend analysis

[sarath5140]

Hi, Can someone help me in solving my problem.

I have two tables and both are not related to each other

Table A Data:

ID                   DateTime                Title

1	7/20/2019 6:06 Am	A
2	7/20/2018 4:04 AM	B
3	1/20/2019 4:04 AM	C

Table B Data:

ID                   DateTime                Title

4	9/20/2019 6:06 Am	A1
5	9/20/2018 4:04 AM	B1
6	10/20/2019 4:04 AM	C1

Now based on these two tables data, I wanted to show the trend based on the datetime column and how many count(ID) were created from both the tables in a single Line chart. 

Please give me the formulas and measures to fix this

[MFelix · Accepted solution]

Hi @sarath5140 ,

Create a table with unique values of DateTime from table A and Table B. Use this new table to make the relationship between both tables then add this to the x-axis and the count ID from table A and Table B on your chart.

Check PBIX file attach.

Regards,

MFelix

[sarath5140]

Hi, Thanks for your reply.

I have tried the below query for getting the distinct dates from two tables, but when converting the column datatype to datetime somehow the dates are not matching.

For example, my table A  datetime column one value looks like this 2/26/2019 10:10:41 AM 

after transforming the datatype to datetime using the below formula

{

Table.TransformColumnTypes(#"Renamed Columns",{{"CreateDate", type datetime}})

now my datetime in the merged table looks like this

2/26/2019 3:40:41 PM 

I think it is taking the GMT time after conversion and because of this all the datetime values are not matching and in axis of trend not showing any dates. Could you please help in getting the exact datetime match in my trend