Forum Discussion

Fro88er's avatar
Fro88er
Icon for Helper IV rankHelper IV
5 years ago
Solved

Date and Name Table that grows

There are two tables, Date and Users.  I am trying to create a third table or virtual table, of Month Start Dates, and User Names for each month.  I can then add a column or measure of Phone calls, and meetings.  Everyone needs to make 80 calls, 60 meetings, I can do this part. Basicly this is a User monthly target table, that will measure actual data, calls and meeting from another source.

 

The challenge is building this new table for each User and each StartOf Month; expecially when a new User Name (we hire someone) is added is populated to the User Table.  The new employee/user automaticly gets added this (virual, M or DAX) table at each StartOfMonth Date table. It does not have to be smart enough, to read the employee start onward, have an HR table that will not "count" calls/meetings from prior periods.

 

 

Date Table User Table Expected Result
StartOfMonth Name StartOfMonthNameCallsMeetings
1/1/2021 Mickey Mouse 1/1/2021Mickey Mouse8060
2/1/2021 Mini Mouse  2/1/2021Mickey Mouse8060
3/1/2021 Donald Duck 3/1/2021Mickey Mouse8060
    1/1/2021Mini Mouse 8060
    2/1/2021Mini Mouse 8060
    3/1/2021Mini Mouse 8060
    1/1/2021Donald Duck8060
    2/1/2021Donald Duck8060
    3/1/2021Donald Duck8060
  • In Power Query, start with the Date table.  Add a custom column. In the formula box, put the name of the User Table. It should autocomplete the name.

    On the next step, expand the column from the icon in the heading.

    That should be it.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion

    In Power Query, start with the Date table.  Add a custom column. In the formula box, put the name of the User Table. It should autocomplete the name.

    On the next step, expand the column from the icon in the heading.

    That should be it.

  • Wow, that was soo easy worked liked a charm!

     

    Thank you so much, been trying to work through this for weeks.