Staffing Projection / Allocation Using CrossJoin

[Anonymous]

I'm looking to compare projected staffing data with allocated data. The projected data is summarized by month (example below)

Project No.	Month	Employees
202001	01-01-20	4
202001	02-01-20	6
202001	03-01-20	9
202001	04-01-20	12
202001	05-01-20	16
202001	06-01-20	20
202001	07-01-20	22
202001	08-01-20	22
202001	09-01-20	22
202001	10-01-20	22
202001	11-01-20	22
202001	12-01-20	19

The allocated data is listed as Start Date, End Date, and Percent Allocated (example below).

Employee Name	Start Date	End Date	Project No.	% Allocated
Employee 1	01-01-20	01-01-21	202001	100%
Employee 2	01-01-20	01-01-21	202001	100%
Employee 3	01-01-20	01-01-21	202001	100%
Employee 4	01-01-20	01-01-21	202001	100%
Employee 5	02-01-20	01-01-21	202001	100%
Employee 6	02-01-20	01-01-21	202001	100%
Employee 7	02-01-20	01-01-21	202001	100%
Employee 8	03-01-20	01-01-21	202001	100%
Employee 9	03-01-20	01-01-21	202001	100%
Employee 10	04-01-20	01-01-21	202001	100%
Employee 11	04-01-20	01-01-21	202001	100%

I'd like to make a Line and Stacked Column Chart Visualization comparing projected with allocated. I was thinking I could make a calendar table and then try to CrossJoin all the data together, but I'm running into some issues because of lack of experience. Not sure if I can crossjoin 3 tables together or if that is even the right approach.

Here is an old post I made with a similar question/resolution (but without stacking data):

https://community.powerbi.com/t5/Desktop/Summarizing-Values-At-Weekly-Intervals/m-p/748269#M360676 

Any help is appreciated.

[v-frfei-msft]

Hi @Anonymous ,

Does userelationship meet your requirement?

[amitchandak]

Create a calendar table with the month start date. 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

Now refer Active employee count and the table created month-wise in blog (pbix) -

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

Create a summarize table with month start dates, employee name, and allocation %.

Join this new table with a date table and the first project table with date.

Share a pbix with some sample data. In case it still does not work out.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601