Display Exposure Using Project Number, Start/End Date, and Amount

[Anonymous]

I have the following data for multiple companies:

Company	Project	Amount	Start	End
X	A	2,000,000	10/1/2015	9/30/2016
X	B	5,000,000	5/15/2016	5/15/2017
X	C	20,000,000	10/1/2015	10/1/2017

I would then use that data to find the total number of months (decimal) and devide the [Amount] across that time. Then, using a bar chart (or something similar) I would like to show the total cost per month.

I've tried playing around by linking to a calendar table which allows me to show [Amount] by associated [Start] and [End] date, but not get the flow of money from month to month.

Thanks for any help!

[v-huizhn-msft]

Hi @Anonymous,

You can turn start and end date into one column by adding a custom column to store the date range, then expand the date list. 

Please select add custom, type the formula below.



Here is my statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilDSUXIEYiMdAwMDEAayDQ30DfWNDAxNgWxLfWMDENtMKVYHotoJiE2RVJvqG5pCVCDY5nDVziCzDXAYDmMDlccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, Project = _t, Amount = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Project", type text}, {"Amount", type number}, {"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Start],Duration.TotalDays([End]-[Start]),#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

You will get the following table, and create a relationship with calendar using custom column.



Then you can get flow of money from month to month.

Best Regards,
Angelia

[Anonymous]

I've tried your solution, however it looks like my Oracle data source doesn't like the List type in the column. When I try to apply the query I get the below message.

[v-huizhn-msft]

Hi @Anonymous,

Please add custom column date type is equal to the date type in your oracle database.

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft

I'm now getting an error saying "[Expression.Error] We cannot convert null to type Number". I'm using the following process:

When I create the custom column, I'm currently using the below formula becuase some rows have empty dates resulting in errors.

 

Once I apply the custom column, I'm left with the below column that I expand:

Finally, I replace all "null" with "01/01/0001". However, I'm still receiving the message.