Multiple date columns in different tables

[Daimy]

Hi all, I'm new to powerbi, so excuse me if this question has been asked several times. I couldn't find it in the threads.

I have four different tables: 

1. Projects - This is de main table i want to work from

2. Hours (associated with projects)

- This table has an selling amount (total value of hours made on project) on a specific date

3. Expenses (associated with projects)

- This table has an selling amount (total value of expenses made on project) on a specific date

4. Invoices (associated with projects)

- This table has an selling amount (total value of invoices sent on project) on a specific date

I would like to create a matrixtable where i can look at all the projects and then one timeline in where the three above tables are shown. Right now every table is connected to hes own date field. I want to have 1 datefield on project where are those tables are connected to. 

For example:

                              January                                  February

Projects | Hours | Expenses | Invoices |  Hours | Expenses | Invoices | 

A             1000        5000        0              5000       3000       14000

B              500         500          500           0             0             500 

The problem is that as soon as I add a datefield as a column to the matrix. Depending on which column I add, only one of the three values ​​is put in a timeline. And i want all three values to look at the same datefield. 

Thanks in advance

[ERD]

Hi @Daimy ,

Do I understand correctly that your Hours,Expenses,Invoices tables contain a date field and they are connected to your Project table via some ProjectID/Project name field?

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Daimy]

@ERD Correct. All three tables have in common that they are linked to Project via ProjectID field. All three tables have a selling amount and a date field. 

[ERD · Accepted solution]

So you need to create a proper Calendar (Date) table and connect your Hours,Expenses,Invoices tables to it via Date field.

Here is an example how your model will look like:

Matrix:

Date from Calendar table in columns, Project from Project table, Hours amt from Hours table, Expenses amt from Expenses table.

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Daimy]

@ERD It works! Thank you so much. 

[v-xulin-mstf]

Hi @Daimy,

Do these four tables have common columns, for example 'Project_id'?

Then you can merge these four tables by the common column in power query.

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Daimy]

@v-xulin-mstf Thank you for your answer. I just tried it and at first it looks like it works. But when i add the new columns to the matrix the values seem to change. Example: Cost in total is 529k, when merged the fields add up to 27Million. 

[v-xulin-mstf]

Hi @ERD,

Does the total contain duplicate values?
You can create measure for grouping to calculate totals.

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@Daimy is it possible for your to share sample data or sample powerbi file for each of the table, I will try to recreate the same structure in my powerbi file and see if that can help you. Pl. share data in a format which I can copy from.

[Daimy]

Hi Negi007,

Unfortunately I have no sample data. I cannot share the data I use, it is confidential. I am attaching two screenshots. I hope that gives some more clarity. The first screenshot shows the values in the matrix. As you can see the amount of hours is changing every month. That is because i choose a date column out of the same table the hours are written (see matrix 2). The other two values are not changing because they are connected to their own date columns in their own tables.

[negi007]

@Daimy  i would suggest you to create a sepeate date table and then link main date table to other date column in all tables. Then you only need to use the date field from the date column to filter data. 

You can simply create a date table with below syntex

Calendar = CALENDARAUTO()

thea above function returns a table with a single column named "Date" that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.

you can refer to below links for further help if needed 

https://docs.microsoft.com/en-us/dax/calendarauto-function-dax

https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

let me know if you still need help

[Daimy]

Hi Negi007, 

I tried the things you said but it is not working. 

Projects got a relationship with the following tables: 

- Invoices (that has a date column)

- Expenses (that has a date column)

- Hours (that has a date column)

I tried making a relationship between the three tables with date column to the newly made Date Table. And then putting the newly made date column into the matrix. It still shows totals and not the values over time. 

I think the problem is that Project table does not have a date field. The three tables above must have a relationship with projects because it is data from Dynamics 365 (matching on Value and _ID). 

Do you have any other tips?