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