Forum Discussion
Linking dates - Different tables / data sources
Hi
I'm working on a report which shows timesheet hours against actual clocked hours from a timeware program.
The timesheet is a PowerApp I've created with data stored on a sharepoint list. The timeware program sits on a SQL database.
I have a link between names (this will change eventually to a unique id) on both systems. I want to select a date on the date filter which then shows the timesheet hours and actual hours for that date, or be able to set a date range ie selecting a Sunday shows data for the previous week.
The date columns in both datasources are set to date / time. My date filter is linked to the date/time column of the timeware db. Although I have joins on the tables the date filter is not working for the timesheet data.
The total of the "Actual" column also does not sum to just the items in the table
Any help much apprecitated
sanderson82 how I know whcih table is which but few ideas.
- make calendar/date dimension in your model and link it with your tables. There are many posts on how to create one.
- make employee dimension table (unique employee list) and link with your tables.
in all visuals, use date and employee from above dimension and data from your transaction tables and everything will work thru.
Date and employee dimension will be one to many relationship, one on dimension side and many on transaction side.
7 Replies
sanderson82 can you share relationship diagram, it is not clear how these tables are related and what kind of relationship is this? One to many, many to many etc?
sanderson82 how I know whcih table is which but few ideas.
- make calendar/date dimension in your model and link it with your tables. There are many posts on how to create one.
- make employee dimension table (unique employee list) and link with your tables.
in all visuals, use date and employee from above dimension and data from your transaction tables and everything will work thru.
Date and employee dimension will be one to many relationship, one on dimension side and many on transaction side.
