Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leopoldvili_1
Helper I
Helper I

Calculate from start end End date duration by each Month.

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.

leopoldvili_1_0-1656856733505.png

 

 

 

Best G

1 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"

vkkfmsft_2-1657077848113.png

 

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.

View solution in original post

7 REPLIES 7
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Hi @leopoldvili_1 ,

Nathaniel_C_0-1656861103571.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @leopoldvili_1 ,

Nathaniel_C_1-1656861278579.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

leopoldvili_1_1-1656869118037.png

 

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.

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"

vkkfmsft_2-1657077848113.png

 

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.