Compare data from two different sources, without being able to merge the queries.

[jeroenwmwillems]

Hi there,

I'm struggling with an issue that seems simple to me, but I haven't been able to solve so far. I was hoping one of you legends could help out.

I have two dfifferent data sources:

1. A data source with that has a row for every day of the year and on that row a number of active users on that day.

2. A data source that is more substantial, with a row for every order. One day can have more than 1000 orders. 

Merging the sources at this point would be incorrect, since source 1 has dates in GMT+10 and source 2 has dates and times in GMT+0. After I have edited the data, I change the date format.

Now I have two matrixes next to each other. The first is using data only from source 1, with weeks as rows and weekdays as columns, the active users as the value. The second using data from soource 2, weeks as rows, weekdays as columns and the sum of orders as values. 

I would love to create a third matrix, dividing the values in matrix 2 by the values matrix 1, giving me a conversion rate. 

Any suggestions how to do this best and in a simple manner without having to change too much?

Cheers,

Jeroen

[amitchandak]

Create a common time Dim.

1. Have a truncated date at both sources.

2. Either in GMT +10 make a date GMT_10 -10=GMT 0  or have time dim with two columns, date_GMT_0, DATE_GMT_10,

3. Join this common time dim to both the tables.

4. You can generate time dimension is power BI, if needed

[Greg_Deckler]

Sample data and expected output please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490