Forum Discussion
Multiple date columns in different tables
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 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?
