Is it possible in Power BI to calculate forecast from start / end date, duration and total amount?

[Anonymous]

Hello I would like a help whether it is possible to calculate a forecast for each ID, if I have a the following data: start date, end date , total amount and duiration (over no. of months): 

but I would like in Power query get this : 

I was looking through the forum but could not find solution. 

Thanks 

[Ahmedx · Accepted solution]

you can do this in power query
Sample PBIX file attached

[Anonymous]

Hi @Anonymous ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Total amount", Int64.Type}, {"Start date", type date}, {"End date", type date}, {"Duration", Int64.Type}}),
    #"Inserted Division" = Table.AddColumn(#"Changed Type", "Monthly forecast", each [Total amount] / [Duration], type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Division", "Custom", each {1..[Duration]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each Date.AddMonths([Start date],[Custom]-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Total amount", "Start date", "End date", "Duration", "Custom"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
    #"Changed Type1"

Hope this helps.

[Anonymous]

Hi thank you but I can not open the file: 

 Would you mind just sharing the code. Thank you 

[Ahmedx]

pls try

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyNDIAkl6JebpGJkCGS2oyhGFoBCIMlGJ1gAqdQGwLkEL/5BKIPFiHKZAB4piYKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Total amount" = _t, #"Start date" = _t, #"End date" = _t, Duration = _t, #"Monthly amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Total amount", Int64.Type}, {"Start date", type date}, {"End date", type date}, {"Duration", Int64.Type}, {"Monthly amount", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Start date", "End date", "Monthly amount"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Date", each List.Generate(()=>[Start date],
(x)=> x<=[End date],
(x)=> Date.AddMonths(x,1))),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Start date", "End date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Date", "Monthly amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})
in
    #"Changed Type1"

[Ahmedx · Accepted solution]

you can do this in power query
Sample PBIX file attached