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. ...
Need Help
Show and Tell
Tips and Tricks
jennratten's avatar
jennratten
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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!