Revenue split contract revenue over months/years

[JustinNoe]

Revenue is based on potential contracts and I am needing to forecast revenue over X years based on the start/end date. 

DATA:

Contract ID	Contract Name	Value	Start Date	End Date	Months
id0001	(Name)	$1,000,000	01/05/2020	2/05/2021	13
id0002	(Name)	$25,000,000	05/01/2021	05/01/2025	48

Graphically I need to be able to display 2020, 2021, 2022, ect. revenue based on the contracts. 

I tried using GENERATESERIES to make the rows based off of the months but I can't seem to set it as a variable and it must be hard set?

GENERATE ( TableName, GENERATESERIES ( 0, TableName[Month], 1 ) )

I'm at a loss at how to create this data so it isn't hard coded and can easily be expanded as our comany grows. I have tried various DAX exquations and I can't nail it down. 

Any feedback at all would be greatly appreciated.  

[JustinNoe]

Issue was a result of having null data, even though I replaced it as a step in the transforming data option. 

As soon as I edited the data from the source and refreshed I was able to get the values to be displayed correctly. 

Thank you so much for taking the time to assist me with this. 

[v-janeyg-msft · Accepted solution]

Hi, @JustinNoe 

It’s my pleasure to answer for you.

You can do it in pq.

Like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykwxMDAwVNJR8kvMTQXRhgZgAGQZGOobmOobGRiBOUZQDliNsVKsDlSvEVQviDYyRWg21Qfqh6qHc0CKDI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, #"Contract Name" = _t, Value = _t, #"Start Date" = _t, #"End Date" = _t, Months = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", type text}, {"Contract Name", type text}, {"Value", Int64.Type}, {"Start Date", type date}, {"End Date", type date}, {"Months", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
x=(Date.Year([End Date])-Date.Year([Start Date]))*12+Date.Month([End Date])-Date.Month([Start Date]),
l=List.Generate(
    ()=>0,
    each _<=x,
    each _+1
)
in
l),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    Custom1 = Table.TransformRows(
#"Expanded Custom",
(x)=>
[
Contract ID=x[Contract ID],
Contract Name=x[Contract Name],
Value= let z=Table.RowCount(Table.SelectRows(#"Expanded Custom",(m)=>m[Contract ID]=x[Contract ID])) in x[Value]/z,
Start Date= let tab=Table.SelectRows(#"Expanded Custom",(y)=>y[Contract ID]=x[Contract ID]),min=Table.Min(tab,"Custom")[Custom] in if x[Custom]=min then x[Start Date] else Date.StartOfMonth( Date.AddMonths(x[Start Date],x[Custom])),
End Date=let tab=Table.SelectRows(#"Expanded Custom",(y)=>y[Contract ID]=x[Contract ID]),max=Table.Max(tab,"Custom")[Custom] in if x[Custom]=max then x[End Date] else Date.EndOfMonth( Date.AddMonths(x[Start Date],x[Custom])),
Month=x[Months]
]
),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Contract ID", "Contract Name", "Value", "Start Date", "End Date", "Month"}, {"Contract ID", "Contract Name", "Value", "Start Date", "End Date", "Month"})
in
    #"Expanded Column1"

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JustinNoe]

Thank you so much for taking the time to put that all together for me!

I am running into this error message and I can't seem to correct it. Do you have any suggestion as to what may be causing this?

[v-janeyg-msft]

Hi, @JustinNoe 

Can you share the screenshot of custom1's result?

Maybe the result format of custom1 conflicts with the format supported by the fromlist function.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JustinNoe]

I'm curious if it is an issue with my data. Even though I have gone in and replaced the valuues of "null" for some of the start/end dates and value amount (some contracts we don't have information on at this time), is it still seeing the original null value and thus throwing the error?

My appologies, is this the information you're referencing?

[JustinNoe]

I went in and verified that I do not have any null fields in the data table, just to see if that may have caused issues. 

[Fowmy]

@JustinNoe 

You easily split and make it automated in Power Query, please refer to my blog and video to learn more.

https://www.youtube.com/watch?v=O3AnAKjRBLM&t=0s

https://excelfort.com/allocate-amount-monthly-excel-powerquery/

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn