Workload Per Month Before Ship Date

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.