Date Table Relating to Two Tables with Dates

[aekoll]

Hi everyone,

First of all thank you very much for trying to help me. I am relatively new to Power BI and still learning. I am currently facing an issue where I can not wrap my head around.

I have a Datamodel with four different tables in it. I will briefly explain them, but I also attached a sample Power BI Dataset  where I rebuilt my issue with simple excel files. I need to create a report with the timekeeping entries of our workers. For that I have:

• A table with all our workers (users) and their ID. Simple as that.
• A table with all Timeentries per user, day and project. This means that a user worked the specific amount of time on a specific date on a specific project.
• A table with the Weekly total per user and week. The total value is  the calculation of all time that was worked in a week minus the time someone should work in a week for each user. This results in the amount of over- or under (do you say it like that? ;)) time a worker had for each week.
• A date table with the min date of either the weekly total or timeentries (whatever is lower) and the max date of the higher date of the two.

In the end I want to reate a table that shows each user, their total time that they worked (so sum of timeentries) and their current over- or undertime total (so sum of total in weekly total) and to be able to filter that with my date table.

However I can not get the relations between all of those tables setup in a way that this works. I would always end up with a circular relationship that does obviously not work. Do you have any ideas on how to slove this or any approach on how I could find a solution for this.

Thank you so much for your help and time! I really appreciate it. 

[mangaus1111 · Accepted solution]

Hi @aekoll ,

try first to set a relationship 1 to many between data table and weekly totals and then change all the relationships from bi-directional to single directional like in this example:

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

[onurbmiguel_ · Accepted solution]

Hi 

Please take a look a this solution: 

LINK

If this is not what you pretend, please send a draft what should by the final result.

Best regards

Bruno Costa | Continued Contributor

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

[mangaus1111 · Accepted solution]

Hi @aekoll ,

try first to set a relationship 1 to many between data table and weekly totals and then change all the relationships from bi-directional to single directional like in this example:

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

[aekoll]

Hi @mangaus1111 ,

Thank you very much for your suggestion. I changed the relation as per your suggestion and it does work indeed like this. I might have to check if the changed filtering directions had some influence for other graphs in the big report, but so far it looks good! Can't believe I did not try this on my own..