Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Date Relationships between tables

Hi,

I have 2 tables that I'm trying to create a relationship between to do analysis. The first is an Actuals table that has the following columns:
 Category, Project, Name, [Column for each week ending in year,Data has logged time]

This table will be populated weekly by individual members of the team.


The second table is an Expected Table which has the following columns:
Category, Project, Start Date, End Date, [Column with each persons name, Data has expected time to be logged]

This table will be filled out more infrequently(maybe once a month). I want to compare each week in the actuals to the expected here if it is within the start-end date range and matches on Category, Project & Name.

I've imported both these tables into Power BI, unpivoted the Actuals table on all the dates so that it now  looks like this:
Category, Project, Name, Date, Time

 

and unpivoted the Expected table so that it looks like this: 

Category, Project, Start Date, End Date, Name, Time.

How should I go about doing this? I'm a beginner to power bi and any help would be greatly appreciated. 

Thanks, 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    You create dimension table for category and project

    Project = distinct(union(distinct(actual[project]),distinct(expected[project])))
or
distinct(expected[project])


Category= distinct(union(distinct(actual[Category]),distinct(expected[Category])))
or
distinct(expected[Category])

     create a date table

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

    Refer
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

    Join with dates

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

    Best Regards,

    Icey

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      amitchandak  What exactly is the purpose of the two distinct tables? I've succesfully created the dates table and am now attempting to join but I'm still running into issues. 

      Just to be clear this is one of the views I am inteding to use:

      I want employees to log work every day on each project. Management will set up a very broad estimate which will put a value on each week and then apply those values over 1/2 months. So I want my slider to get actual time from the actuals table which is fine. I then want to get my expected values based on whether dates in the slider. So if start date is in between the 2 values in the slider the expecteds for the week will be the values in the table. If expected dates is in the slider for 3 weeks then I want 3x what is in the table, if the slider is a full year and a project has a diff of 2 weeks between start date and end date then the expected should have 2x of what is in the table.

      Sorry I know that I haven't explained this well. Let me know if you don't understand and I can try and rephrase.


      Regards,