Extrapolating Monthly Targets from Overall Target, Start Date and Number of Months

[PBI_Curve]

Hi Guys,

I'm trying to figure out a way to extrapolate monthly targets from the number of months involved, the total target and the start date.

For example, I'm given the three pieces of information for each Lead:

1. The Duration of the Agreed Contract - e.g. 12 months for Seller A.

2. The total number of units the lead expects to sell in total - e.g. 120 units for Seller A.

3. The execution date of said contract - e.g. 1st January 2019  for Seller A.

In this example, I know that this aforementioned lead will require 10 units per month from January 2019 to December 2019 (totalling 120 units). From this I can generate a line graph showing monthly targets.

Basically, I need to turn information like this:

Lead	Contract Execution Date	Total Contracted	Contract Duration (Months)
Seller A	01/01/2019	120	12
Seller B	01/07/2019	120	6

Into this:

Lead	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19
Seller A	10	10	10	10	10	10	10	10	10	10	10	10
Seller B	null	null	null	null	null	null	20	20	20	20	20	20

Is there a way to use PowerQuery and/or DAX to extrapolate this from hundreds of leads with only those 3 pieces of information for each?

All suggestions welcome!

[OwenAuger · Accepted solution]

@PBI_Curve

I would personally use Power Query to take your original table and expand the rows so that there is a row per month for each Lead, with the appropriate monthly value.

Sample PBIX here

Code in my sample file to recreate your original table which I called Leads:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7NyUktUnBU0lEyMNQHIiMDQ0sgx9DIAEwqxerAFTlBFZmjKjJTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Lead = _t, #"Contract Execution Date" = _t, #"Total Contracted" = _t, #"Contract Duration (Months)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Lead", type text}, {"Contract Execution Date", type date}, {"Total Contracted", Int64.Type}, {"Contract Duration (Months)", Int64.Type}})
in
    #"Changed Type"

Code to expand Leads to one row per Lead per month (LeadsExpanded😞

let
    Source = Leads,
    #"Added Custom" = Table.AddColumn(Source, "Contracted per Month", each [Total Contracted]/[#"Contract Duration (Months)"], type number),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each let FirstDate = [Contract Execution Date]
in List.Transform({0..[#"Contract Duration (Months)"]-1}, each Date.AddMonths(FirstDate,_))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Contract Execution Date", "Total Contracted", "Contract Duration (Months)"}),
    #"Expanded Months" = Table.ExpandListColumn(#"Removed Columns", "Month"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Months",{{"Month", type date}})
in
    #"Changed Type"

With LeadsExpanded, you can now easily visualize values per Lead per Month as per your post (sample matrix in PBIX above).

Regards,

Owen