Workload Per Month Before Ship Date

[MoonAlligator]

Hi All,

I'm trying to create a line or area chart showing the workload per month for all my projects before the project ship dates. I have a table of projects with ship dates and a ta

ble of workload amounts per month before a ship date based on project keys. The Project Key column is the relationship between the two tables. I'm not sure what formula I can use to create a line chart that says for Project A, the workload in 8/1/2020 will be .7 and 7/1/2020 will be 1.0 etc...

Project Name	Ship Date	Project Key
A	9/1/2020	High_Usage_2
B	10/1/2020	Low_Usage_2
C	12/1/2020	Low_Usage_2

Project Key	Ship Date -1	Ship Date -2	Ship Date -3
High_Usage_2	.7	1.0	1.2
Low_Usage_2	.5	.6	.6
Medium_Usage_2	.6	.8	1.0

Thanks,

[v-rzhou-msft]

Hi @MoonAlligator 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou 

[amitchandak]

@MoonAlligator , this data is not matching with your requirements

Can you share sample data and sample output in table format?

[MoonAlligator]

Hi,

Sorry for the confusion. I'm not sure what you mean by the data not matching the requirements? The sample data is in table format on the post. Below would be the end goal sample output. As you can see in the chart below if I'm working on all 3 projects, the workload would be highest at the beginning of August. I'm not sure how to get Power BI to do this. Let me know what other questions you have regarding this request.

[v-rzhou-msft · Accepted solution]

Hi @MoonAlligator 

I build two tables like yours to have a test.

Table1:

Table2:

Due to I don't know your calculate logic, so I calculate the sum of each Ship Date before.

Firstly we need to build a new table by Unpivot and Merge in Power Query.

Duplicate Table2 and rename as Table3, select three columns except Project Key column and unpivot. 

Then merge Table3 and Table1 by Project Key column and expand Project Name and Ship Date column in Table1.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8shMz4gPLU5MT403UtJR0jMHEoZ6BmDSSClWJ1rJJ78cWYEpiDCDECBp39SUzNJcZBVgSQuoObGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Key" = _t, #"Ship Date -1" = _t, #"Ship Date -2" = _t, #"Ship Date -3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Key", type text}, {"Ship Date -1", type number}, {"Ship Date -2", type number}, {"Ship Date -3", type number}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Ship Date -1", "Ship Date -2", "Ship Date -3"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Only Selected Columns", {"Project Key"}, Table1, {"Project Key"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Project Name", "Ship Date"}, {"Table1.Project Name", "Table1.Ship Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table1",{{"Table1.Ship Date", "Ship Date"}, {"Table1.Project Name", "Project Name"}})
in
    #"Renamed Columns"

Result:

Build a calendar table and build relatship with Table3.

Calendar = CALENDAR(DATE(2020,07,01),DATE(2020,12,01))

Then build two calculated columns in Table3.

Diff = 
Var _Diff = FORMAT(RIGHT('Table3'[Attribute],1),0)
return
_Diff

Ship Dates Before = 
DATEADD('Calendar'[Date],-1*'Table3'[Diff],MONTH)

Result:

Finally, build a measure, use the measure and Ship dates before column to build an Area chart.

Measure =
CALCULATE (
    SUM ( 'Table3'[Value] ),
    FILTER (
        ALL ( 'Table3' ),
        'Table3'[Ship Dates Before] <> BLANK ()
            && 'Table3'[Ship Dates Before] = MAX ( 'Table3'[Ship Dates Before] )
    )
)

Result:

If this reply still couldn't help you solve your problem please show me more details.

Your calculate logic to calculate the value per month before ship date.

Ex: In the image as below, I know 2020/7/1 = 1, but how can we get 2020/8/1 = 1.3? if you use sum, why 2020/7/1 is not equal to 1+0.6?

You can download the pbix file from this link: Workload Per Month Before Ship Date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.