cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_Curve Helper I
Helper I

Extrapolating 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:

 

LeadContract Execution DateTotal ContractedContract Duration (Months)
Seller A01/01/201912012
Seller B01/07/20191206

 

Into this:

 

LeadJan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19
Seller A101010101010101010101010
Seller Bnullnullnullnullnullnull202020202020

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

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

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

1 REPLY 1
OwenAuger Community Champion
Community Champion

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

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors