Forum Discussion

wadidaw's avatar
wadidaw
Frequent Visitor
2 years ago

Calculate per month in a same row

I need to calculate days per month >>(departure - arrive) +1, but in the table show different month in the same row

I have table like this

And this is the table result i want to have.

 

Any suggestions are very useful for me

Thanks.

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hello wadidaw - this is how you can accomplish this calculation.  Note, the results for the bolded items are the same; the results for the other cells are a little different but the math is correct based on the formula you provided in the description. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ31DcyMDJR0gEyjSHMWB2QuBFC3MgcRcIcSYcBQsbQAGEWkG2KImOKJGMIk4oFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Arrive = _t, Depart = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Depart", type date}, {"Arrive", type date}}),
    #"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type1", "Subtraction", each Duration.Days([Depart] - [Arrive]) + 1, Int64.Type)
in
    #"Inserted Date Subtraction"

     

    • wadidaw's avatar
      wadidaw
      Frequent Visitor
      2 years ago

      Hi jennratten Thanks for your reply, i need to have additional row every each different month period to add 1st day of month (Arrive) and last day of month (depart). I need to calculate per month

      • jennratten's avatar
        jennratten
        Super User
        2 years ago

        You most recent description of what you are trying to achieve seems very different from the expected result originally posted.  Can you please clarify what it is you are trying to accomplish?  Are you trying to add a new column which returns the number of days between the arrival and departure date - or are you trying to add rows for some sort of monthly calculation?  Perhaps you could reply with another example of the expected result.

        Thanks!

         

         

         

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    wadidaw why last row days is 16?

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    days = (from as date, to as date) => List.Generate(
        () => [f = from, t = List.Min({to, Date.EndOfMonth(f)})], 
        (x) => x[f] <= to, 
        (x) => [f = Date.AddDays(x[t], 1), t = List.Min({to, Date.EndOfMonth(f)})],
        (x) => {x[f], x[t], Duration.Days(x[t] - x[f]) + 1}
    ), 
    to_list = Table.ToList(
        Source, 
        (x) => days(Date.From(x{0}), Date.From(x{1}))
    ), 
    tbl = Table.FromRows(List.Combine(to_list), Table.ColumnNames(Source) & {"Days"})
in
    tbl