Workload by Month Based on Ship Dates (Revised)

[MoonAlligator]

Hi All,

Hopefully this request is more clear then the last one. 🙂 I have two data sources! One is a list of projects and their ship dates. The other is the estimated workload per month before the ship date happens. The relationship between the two tables is the Project Key column. What i want to do is create an area chart showing the total estimated workload for every month but I don't know how to make this work. Below are the two data tables:

Project Name	Ship Date	Project Key
A	9/1/2020	High
B	10/1/2020	Low
C	12/1/2020	Low

Project Key	Workload (-1 Month)	Workload (-2 Month)	Workload (-3 Month)
Low	.5	.6	.6
Medium	.6	.8	1
High	.7	1	1.2

Below is the chart I'm trying to create: 

I think this would be the final data table output:

Months	Total Workload
7/1/2020	1
8/1/2020	1.3
9/1/2020	1.2
10/1/2020	1.1
11/1/2020	.5
12/1/2020	0

So for example, this will tell me for Project A in August, a month before the ship date, i'll have an estimated workload of .7 because it's a High on the project key. In July, I'll have an estiamted workload of 1.0 etc...Let me know if you have any questions!

Thanks

[lbendlin]

Something like this?

[MoonAlligator]

Hi,

This is close to what I was looking for however, are you able to change the legend so it's not always by Project Name? Does there have to be a legend for this chart to work? 

[MoonAlligator]

Ah another thing is what if the ship date is not only on the 1st. Is there a rounding thing I can add to the formula?

[lbendlin]

yes, the solution works without the legend too. I just added that to allow you to validate the computation as it doesn't match your expected result.

You can switch from month to day granularity by using a real date table, and slightly modify the time difference checks.

[lbendlin · Accepted solution]

Here is a modified version that will make it easier for you to adjust.  This works with or without the project name on the legend.

Workload 2 = 
VAR d = SELECTEDVALUE(Milestones[Date]) // x axis date - enable "show items with no data"
VAR c = SUMMARIZE(Projects,Projects[Project Name],Projects[Ship Date]
,"WL",SWITCH(datediff(Projects[Ship Date],d,MONTH)
,-3,sum(Workloads[Workload (-3 Month)])
,-2,sum(Workloads[Workload (-2 Month)])
,-1,sum(Workloads[Workload (-1 Month)])
,0))
RETURN SUMX(c,[WL])

[MoonAlligator]

Hi,

I noticed with this formula the column subtotal appears as 0. Is there anyway to fix this? For example i see the following when I create a data table:

Project	Oct	Nov	Total
A	1	1	0
B	2	1	0
C	2	1	0
Total	5	3	0

Thanks

[lbendlin]

A total across months makes little sense in that scenario. Remove the column total display.

[MoonAlligator]

Thanks for your help! The modified version gives me what I was looking for. I'll reach out if I have any follow-up questions. 🙂