Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |