Forum Discussion
Multiple date columns in different tables
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
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.
11 Replies
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
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?
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.
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,
LinkIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
Hi ERD,
Does the total contain duplicate values?
You can create measure for grouping to calculate totals.Best Regards,
LinkIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
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.
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.
