count data across mutiple ranges

[greenmonsta]

Hi All,

I am trying to solve the following in Power Query.

In the data table I have Ticket Number, the date the ticket was created and the date the ticket was resolved. If the resolved cell is blank the ticket is still open. Then the other table is a list of sprints with their start and end dates. For each sprint I am trying to get the count of the tickets that were created, resolved, and remain open. It is the remain open column that I am really struggling with, For example ticket HVSD-19211 needs to be counted as open in sprints 2211, 2212, 2213 and 2214.

tables

[ImkeF · Accepted solution]

Hi @greenmonsta ,
didn't get that requirement before.
You can adjust like this:

let
  Source = Sprints, 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Days", 
    each {Number.From([Start]) .. Number.From([End])}
  ), 
  #"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"), 
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Days", {{"Days", type date}}), 
  Merge = Table.NestedJoin(
    #"Changed Type", 
    {"Days"}, 
    Tickets_Exanded, 
    {"Dates"}, 
    "Starts", 
    JoinKind.LeftOuter
  ),
    Expanded = Table.ExpandTableColumn(Merge, "Starts", {"Ticket Num", "Opened Date", "Closed Date"}, {"Ticket Num", "Opened Date", "Closed Date"}),
    AddOpened = Table.AddColumn(Expanded, "Opened", each if [Days] = [Opened Date] then [Opened Date] else null),
    AddClosed = Table.AddColumn(AddOpened, "Closed", each if [Days] = [Closed Date] then [Closed Date]  else null),
    AddIgnoreForOpen = Table.AddColumn(AddClosed, "IgnoreForOpen", each  [Opened Date] >= [Start] and [Closed Date] <= [End]),
    #"Grouped Rows" = Table.Group(
    AddIgnoreForOpen, 
    {"Sprint", "Start", "End"}, 
    {
      {
        "Tickets Created", 
        each List.Count(List.Distinct(List.Select(_[Opened], each _ <> null))), 
        Int64.Type
      }, 
      {
        "Tickets Closed", 
        each List.Count(List.Distinct(List.Select(_[Closed], each _ <> null))), 
        Int64.Type
      }, 
      {"Tickets Open", each List.Count(List.Distinct(Table.SelectRows(_, (x) => not x[IgnoreForOpen])[Ticket Num])), Int64.Type}
    }
  )
in
  #"Grouped Rows"

Also check enclosed file.

[greenmonsta]

@ImkeF You are the bomb! Thank you! I leaned a lot here, not only your solution but the your style is awsome! Thanks again.

[ImkeF]

Hi @greenmonsta ,
I've used Gregs file, so you'll find both our solutions in it.
The Power Query solution is in Table "Result". 

[greenmonsta]

Hi @ImkeF . Thank you for pointing out the Power Query Result. I see it now. This is so much better than the way I was attacking it. My tables were really wide. This is much more efficient, really great insight. I am not getting the "Tickets Open" column to calculate correctly though. I believe it is taking a distinct count, but if a ticket were opened and closed in the same sprint it should not be counted. Any thoughts on how to solve this?

[ImkeF]

Hey @greenmonsta ,
please check the file enclosed (edited on July 23rd). 

[greenmonsta]

Hi @ImkeF , Appreciate the feed back. Unless I am missing something(and I very well could be) this is still a Dax solution. I'm not using Powe BI. I am using Power Query to prepare the data for output into an Excel spreadsheet. I think I have a solution though I have not fully varified my results yet. This is executed while in the Sprint date table in Power Query. The big change in thought for me was comapring the Created date to the Sprint end date as oppsed to usint the Sprint start date for comarisions. @Greg_Deckler  I will update this thread once I have had more time to verify my reults.

= List.Count(
Table.SelectRows(
Add_Sprint_End,
(IT) =>
IT[Resolved] > [End Date] and
IT[Created] <= [End Date]
) [Merged]
)

[Greg_Deckler]

@greenmonsta I don't have a Power Query solution for this but I did create a DAX solution for this a long time ago. 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[greenmonsta]

Hey Greg, Thanks for the fast reponse. I really need it in Power Query but I am looking at the Dax. Maybe I can get some hints out ot there to apply to my M code.

[Greg_Deckler]

@greenmonsta Yeah, I'm not sure how you would go about that in Power Query. Maybe @ImkeF has some thoughts.