Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

I have the following data for multiple companies:

CompanyProjectAmountStartEnd
XA2,000,00010/1/20159/30/2016
XB5,000,0005/15/20165/15/2017
XC20,000,00010/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!

 

Need Help

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,


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

        Best Regards,
        Angelia