Forum Discussion
Helper IExtrapolating Monthly Targets from Overall Target, Start Date and Number of Months
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!
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.
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
1 Reply
- OwenAuger
Super User
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.
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