Data Model Connections

Hello everyone,

I am working on a dashboard for a financial monthly reporting package and I'm using the Power Query to clean and prepare my Trial Balances and General Ledgers, load them into my data model so I can use pivot tables later on to summaries and analyze the data,

I am trying to create a relationship ( which is the date ) between my two sources of data ( Trial Balance, and General Ledger ), in order to use a slicer at the end to filter pivot tables and look at the data on different months and so on,

I've attached a sample of my work file to better explain the issue,


is there any other different way that i can structure my data in a way that i can make excel identify this data set belongs to January, the other belongs to February and so on without using the column so that I may create the connection I need between the data sources ? or a workaround this so that eventually once I use a slicer to change the Date and Filter the figures that I want to look at I can only use one slicer in stead of several for each data source


any help is appreciated, thank you.

Why do you want the date from GL when your TB has already got the date? The fields missing in TB and available in GL are the Classes. That's what you should want to merge, shouldn't you?

Hey,

thanks for taking the time and replying,

that is not the GL, I haven't added yet I am just providing a sample of what the 2 data sets would look like and the common factor between them, if you think it would help make it clearer I don't mind providing the full GL and TB data sets

I Don't think merging would solve anything, the 2 data sets should be separate as they will provide different info for example the TB is going to give the Balance at the end of January for the Account "Customer Receivables", which I will use in pivoting a Dynamic balance sheet,
while the GL would provide me with the breakdown of that figure (names of the customers), which I will use in other analysis, the two need only to be linked through date,
so that when I click on each month, the data shown in pivot tables is consistent and both are related to the chosen month

Yeah, it would be more helpful if you could provide the 2 data sets that correspond to each other. No need the full GL or TB, just some sample data that share the same Account Codes and Months.

i am trying to attach the file but it is larger than 1 MB, also tried trimming it down but still its around 1.2-1.4 MB, is there any other way that i can send you the file ?

Attach only data for say 3 Account Codes x 3 Months for both sets of data.

appreciate it man,

it is attached, waiting for your comment

I need your data, not the PivotTables.

Apologies I thought I loaded them into tables from the Power Query, fixed.

Don't know where you extracted the Source1 sheet, but all the data are here. You don't need the rest of the sheets.

I have taken the liberty to put up BS and PL for you using PivotTable based on the data in Source1 sheet.

If you want to see further details for say Sales (in cell M6) > right-click on the cell > Expand/Collapse > Expand > Acc. Desc. > OK.

I am not able to test it out for you as my laptop is quite slow.

Again I appreciate you taking the time man,

the source1 is basically created by me using the Trial Balances and added manual calculations to it,
and yes I can create a dynamic balance sheet and income statement using it but still for example if I wanted to show the daily sales volume on monthly basis ( coming from POS Report ), as well as the breakdown of the sales ( coming from GL ),
I would need to connect the other sources to it, and that is for the ultimate goal that when the dashboard is final and ready, one slicer for the date and with a click of button choosing the month, i can see all the relevant data for that month, without having to use more than one filter and slicer for each data set,

if you tried to create a relationship between the data sets loaded on the model, you will get the error I am getting regarding the duplicate values, which i completely understand but i was hoping you could show me a work around this error or a different way that i can construct the data so that eliminate this error

I've created a dashboard on the same basis i can share it with you if you would like, and it involved the Revenue breakdown for example, but i had to add it manually and perform a lot calculations and Vlookups and add it as a column to the Source1 sheet ( hence having 1 data source for the pivots and can update everything using 1 slicer )
which is why i am trying to create the connection, it would eliminate all of this manual work and save a great deal of time.

As an accountant with F&B and FMCG background, I have never try to mix my sales reports with financial statements. It's not a good idea. There is simply too much data from the POS system. And if you are going to generate combined reports using Excel, I am afraid your Excel may not cope.

In your sample database, you have almost 1,000 rows of data every month for 2 branches (not sure how many branches you actually have). If you are going to use daily sales instead of monthly sales figures, the data will grow to 30,000 rows per month with only 1 sales category.

Better to keep financial statements and daily/monthly sales reports separate. That's my view.