Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanderson82
Helper I
Helper I

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

Weekly.png

 

Daily.png

 

Any help much apprecitated

 

1 ACCEPTED SOLUTION

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

@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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 

Please see belowRelationships.png

 

Diagram.png

 

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k 

I did create a date dimension table however this still did not work for me.  I ensured the date column was set to date / time for all.  I am guessing I perhaps had the wrong relationships, I will attempt again based on your advice

@sanderson82 you need to change data type in your transaction table to date not date time. I believe in date dimesion you date column data type is date, even if it is date/time, time will always be midnight and your relationship will not work, so better to change everything to date type.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks @parry2k  this is now working.  Just need to figure out how enable selecting a date range, or total for the previous week when a Sunday is selected

@sanderson82 it can be achieved by time intelligence DAX function, there are lot of posts on it. If you couldn't figure out, let me know.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.