Forum Discussion
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 | StartOfMonth | Name | Calls | Meetings | ||
| 1/1/2021 | Mickey Mouse | 1/1/2021 | Mickey Mouse | 80 | 60 | ||
| 2/1/2021 | Mini Mouse | 2/1/2021 | Mickey Mouse | 80 | 60 | ||
| 3/1/2021 | Donald Duck | 3/1/2021 | Mickey Mouse | 80 | 60 | ||
| 1/1/2021 | Mini Mouse | 80 | 60 | ||||
| 2/1/2021 | Mini Mouse | 80 | 60 | ||||
| 3/1/2021 | Mini Mouse | 80 | 60 | ||||
| 1/1/2021 | Donald Duck | 80 | 60 | ||||
| 2/1/2021 | Donald Duck | 80 | 60 | ||||
| 3/1/2021 | Donald Duck | 80 | 60 |
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
Community 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.
- Fro88er
Helper IV
Wow, that was soo easy worked liked a charm!
Thank you so much, been trying to work through this for weeks.