Time intelligence/Data Modeling Issue

[Boycie92]

Hi all,

I was hoping someone could help me.

I have two data sets that I am trying to link together and visualise within a matrix table.

I have source A Sales records from one company. Source B contains sales records from another company.

Information:

Source A:

• Is connected to the model using the folder option. I have a list of CSV files that have been merged together
• This data is not aggregated each sale appears as one record
• Some CSV files contain three months’ worth of sales. Others contain monthly sales and some contain half a month of sales
• Each CSV file has a start and an End date
• Has a Unique id for each sales. Such as A1, A2, A3

Source B:

• Is set up the exact same way. However each CSV file has a month worth of sales
• Each CSV file has a start and an End date
• Has a unique Id for each sales such as B1, B2,B3

Source C:

Contains the address of each sales location and brings in each unique ID. So 123 downtown would be equal to A10, within source A and B50 within Source B.

I have created a relationship between source A and B by using the unique ID’s within Source C. Each relationship is one to many with a cross filtering direction of both

I have created the matrix visual using:

• the end date from source A. Broken down into year and quarter using the hierachy option
• The sales total form Source A
• Both Unique I’d columns form Source C
• The sales total from source B

The problem I am having is that when I bring in source B sales total from each Quarter I am getting the high level total and not the low level total for that quarter.

If I also bring in the End date form source B I get more rows that when summed do not add up to the whole total

I assumed this was relationship issue but it seems as though my model get confused by all the different dates.

It should be able to see that 28/2/2017 from source B should fall within the same quarter as 31/3/2017 from source A but it doesn’t

The matrix visual should look smoothing like this:

Year (A)                Quarter (A)         Unique ID A        Unique ID B        Total Sales A       Total Sales B

2017                      Qtr 1                      A20                        B45              100,000               200,000

Does anyone have any ideas why it reacts like this and how to fix it?

Thanks for the help,

Boycie92

[Ashish_Mathur]

Hi,

Please share the link from where i can download your 3 datasets

[Boycie92]

Hi @v-huizhn-msft and @Ashish_Mathur

The following link will take you to an example i have created:

https://www.dropbox.com/s/nb0pv07lpqpf2ag/New%20Compressed%20%28zipped%29%20Folder.zip?dl=0

Example 1 Is my current layout and problem

For Example, Total A should be  1,90,000 and Total B should be 334,500 but if I export the data as a CSV file Total A is 5,260,000 and Total B is 599,000

Example 2 is @v-huizhn-msft solution. I have created a calendar table and linked it to the Source A End Date and I still get the same results as before. Unless I have created it wrong?

If I don’t create the relationship I get an error within the visual.

Any help would be much appreciated.

Thanks,

Boycie92

[Ashish_Mathur]

Hi,

I cannot get your desired structure but i have got the numbers right.  Download my folder from here.

Hope this helps.

[Boycie92]

Hi @Ashish_Mathur

Thanks for getting back to me.

Your solution would work as it does get the numbers correct however the layout I described is essential. The goal here is to compare both Sources by year and quarter. Sadly your solution does not achieve this.

Can you think of anything else that would be able to help me?

Also I was wondering if you knew the M code version of EOMONTH? So I can calculate the last date within a month?

Your help is much appreciated.

Thanks,

Boycie92

[Boycie92 · Accepted solution]

Hi all,

In case anyone else has a similar problem I have found a solution. However it’s not very elegant.

Solution:

• Source A remains connected to source C using its UniqueID
• Source B remains connected to source C using its UniqueID
• 3 Date tables are created
• Source A connects to one Date Table (DateA) using the End Date (source A) and Date (DateA)
• Source B connects to another Date Table (DateB) using the End Date (source B) and Date (DateB)
• Both DateA & DateB connect to the final Date table (DateC) using their Date columns

From there I was able to get the desired layout and correct figures.

Thanks,

Boycie92

[v-huizhn-msft]

Hi @Boycie92,

You related sourceA to sourceB using the unique ID in sourceC instead of date. Maybe the end date of a record A related to recordB is in first quarter, while the end date of recordB is in second quarter. So for your sceanrio, I suggest you create a Calendar table including all dates in sourceA and sourceB. Please review Calendar function: https://msdn.microsoft.com/en-us/library/dn802546.aspx. 

Then select the Calendar[Date], Unique ID A, Unique ID B, Total Sales A, Total Sales B in matrix and check if it works fine.

Best Regards,
Angelia