Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Generating a Normal Distribution for Budget by project when given start anGenerating a Nod end dates

How can I take a table of projects, that features a budget value, and a start and end date, and generate a normalized distribution by month for every project?    Ideally, this would generate a new ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

This is one way we did last year, average the budget to day level, connect the table to Date table then do further analysis

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZZNbl0hDIW3Ur1x9C62+d1F5lFGVSedVKq6fxWDARuIMgjiHgzHh498fb0+//75/evnvx/w+ngBBtd+6t8OnvqLDgpPxAewDhBe3x9LgzzlnNHIZ3WiPK7wgIyG6pRfkrqsSw8UF+oAC5epg2Qkvk6R3RnpKnhWaatZieeveCkk2VgwkqgkQxP6ynUCxABvNKlOBWUAOdG0OmMQjSbXqbSZlviz5maQ49i9lba20fTtoNShUwPd3hBTLt3q8ACMA9Wd3UwA9pQ251L/rrngIg9sf+CWgzwqQepB2mwAUmfSLWqLZVnA2g1e+S2aOG1YPTYa3ng6o9Byzdb5M9fAWchnmXackZ7tOKkly9YpMwpB+rW5zVnIRgNunifPqkbT7mO6XIcgonwWQv4wnpe7FaIRdLQaQ4R32b2rTOh1N5kJQ5ehFIDeWrhcWNRceJfmtGM3BhoyV6uDbGWaDV027yDoO7hV03xYsjj7hdd+oWHEu4gsKUrcbiIaTDQZroCrtNtIoSFFlw2mojQgXKoZWMy+0QzwyJWVkcFFk3HUcWyyNv5mCRlg2E0G/QpYZNCZEnIS/gENf1pCGhpb38SSjjjLANLcWLKsCZouMo0OW63fgCIySw9S9NiKUX/xupHb0QxApiOgENJ7uMk0QmY19TjE+ytsKDKTHCZHhpG2215zpFcz3ObW55MJXqMEZrVlpJ89NDIdEpHBtLyHhM62eYUSeGfuRt0Us7W4KJbEEyVeoURk85aCvqXWSa9QomQL/Xjtm9cogcEEdQHK9ZZ6jRKRRZWSeQG2aholq9pMCTfg8mx4jRKwKSEh0OWfiKBRAhMl8x+jmpebJUGjZNtk0C9HRcn3fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Total Budget" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Total Budget", type number}, {"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DayLevel", each [Total Budget]/(Number.From([End Date]-[Start Date])+1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "DatesList", each List.Transform( {Number.From([Start Date]).. Number.From([End Date])}, each Date.From(_))),
    #"Expanded DatesList" = Table.ExpandListColumn(#"Added Custom1", "DatesList")
in
    #"Expanded DatesList"