Calculate from start end End date duration by each Month.

[leopoldvili_1]

Hi, I am trying to calculate the duration from start and end dates by each month.  I have only the start and end date table, and I would like to have similar output as on provided screenshot, but I'm having trouble making it happen in Power Query.  can anyone have any solution? Thank you in advance.

Best G

[Nathaniel_C]

Hi @leopoldvili_1 
Not sure what you want here.

How is the seventh row 30 days for instance?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[leopoldvili_1]

Hi @Nathaniel_C  It needs to calculate days from the list.  As the seventh row is the 1st of April and the previous date was March 31 - The output should be 1 day. Thank you for your reply.

[Nathaniel_C]

Hi @leopoldvili_1 ,

Like this?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @leopoldvili_1 ,

I don't know your level of expertise, so, go to Power Query, Home tab, select New Source, Blank Query. Then go to Advanced Editor and paste the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3Mtc3MjAyUtJBYsfqoMsYG2CXMdI3MsUuY6xviFMCZk0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, List = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type date}, {"Start", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Range(
   Source[List],
   [Index]-1,
   1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Values",{{"Custom", type date}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Custom", #date(2022, 1, 27)}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each Duration.Days ([List] - [Custom] )),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom.1", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Start", "List", "Custom.1", "Index", "Custom"})
in
    #"Reordered Columns"

Basically your steps will be to add an index, get the value from the previous row and get the dif.  Once you have pasted the code to Advanced Editor, you will see the steps on the right of the screen.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[leopoldvili_1]

Hi @Nathaniel_C  second part looks perfect. Thank you for your reply. But still have a problem with first part.

I have an initial table where I have only created and resolved dates. 

TicketN	Created date-Start	Resolved date-End
1	1/27/2022	4/14/2022
2	5/22/2022	5/27/2022
3	5/23/2022	6/23/2022

I need to answer how many days was opened ticket 1 in January, in February, March and April. for TicketN=2 I need to anwer only for May as resolved date is in May. For TicketN=3 I  need to asnwer how many days were opened in May and June.

My ideal outcome should look like this for ticketN=1: as in screenshot

Where I can calculate that in January it was opened for 4 days, in February 28 days, In March 31 days, and in April 14 days. I should calculate the same for each ticket. Thank you in advance.

[v-kkf-msft · Accepted solution]

Hi @leopoldvili_1 ,

Please open a blank query and paste this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYrBDcAwCMR24R1EOaAdBrH/Gg2KyM9nXyYpLVLBJ3iAzS7qh2sltQkBpsZ9drVjbOp7uatv04ONTYeDlap+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TicketN = _t, #"Created date-Start" = _t, #"Resolved date-End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketN", Int64.Type}, {"Created date-Start", type date}, {"Resolved date-End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateList", each let
    enddate = [#"Resolved date-End"], startdate = [#"Created date-Start"]
  in
    let 
      MonthEndList = List.Generate( () => [ y = startdate ], each [y] < enddate, each [ z = Date.AddDays([y], 1), y = Date.EndOfMonth(z) ], each [y] ),
      MonthStartList = List.Generate( () => [ y = enddate ],each [y] > startdate, each [ z = Date.AddDays([y], -1), y = Date.StartOfMonth(z) ], each [y] ),
    CombineLists =
      List.Sort( List.Combine( { MonthStartList, MonthEndList } ) )
      in List.Zip({CombineLists, List.Numbers(1, List.Count(CombineLists))})),
    #"Expanded DateList" = Table.ExpandListColumn(#"Added Custom", "DateList"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded DateList", {"DateList", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "DateList", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"DateList", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DateList", type date}, {"Index", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Duration", each let 
  CurTicket = [TicketN],
  PreviousDate = try Table.SelectRows(#"Changed Type1", each [TicketN] = CurTicket){[Index]-2}[DateList] otherwise [DateList]
in 
  Duration.Days([DateList] - PreviousDate))
in
    #"Added Custom1"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[leopoldvili_1]

@v-kkf-msft wow, Thank you very much. It works perfectly!