Number of tickets created in a certain timeframe

[ImHereToLearn]

Hello,

I've been looking for the solution to my issue for a couple of days now with no success and so i decided to check with the Community for help.

I am preparing a sprint report for my team with couple of Excel files as data sources + couple of lookup tables that are hopefully optimizing the performance of the report.

Due to some restrictions, i had to create a separate table (Created Date Lookup) that contains the ticket ID (Key) and the date/time value of ticket's creation (Created):


I have a "Sprint Details" lookup table with Sprint Number, Sprint Start Date and Sprint End Date:

I am updating all data as soon as the sprint ends, so all data is historical data.

What i was trying to do was to determine how many new tickets my team is creating each sprint - in order to see what is the ratio of tickets done vs tickets created. So basically to check each row (ticket) from the "Created Date Lookup" table and compare it with each row of the "Sprint Details" table (if the date is in between sprint start and sprint end).

In my attempts i have tried creating measures and calculated columns. I was playing around with DatesInPeriod, DatesBetween, Switch, IFs, etc.

I hope i described my issue properly, however i am fairly new to PowerBI and so i am not sure as to what is more/less useful.
Thank you!

[Sergii24 · Accepted solution]

Hi @ImHereToLearn, from your description I suppose that the first issue you have is to associate a ticket to a corresponding sprint. There are multiple ways to achieve it, below I will show you one using calculated columns.

Here is the same code to copy-paste:

Sprint = //result of measure should be a single value, so we need to perform few actions
    MINX(                                               //step 3: because measure should return a single value we need to summarize a column. We know that it will contain only 1 row, but Power BI doesn't know it. We can use any aggraation function. In this case I'm using MIN, but MAX will provide the same result
        SELECTCOLUMNS(                                  //step 2: selecting only 1 column as "Sprint Details" table has multiple columns. We need to give it a temporaty name, lets use "@Sprint"
            FILTER(                                     //step 1: filtering the whole "Sprint Details" table to obtain a sprint where "Created" date is between "Sprint Start" and "Sprint End"
                'Sprint Details',
                AND(
                    'Created Date Lookup'[Created] >= 'Sprint Details'[Sprint Start Date],
                    'Created Date Lookup'[Created] <= 'Sprint Details'[Sprint End Date]
                )
            ),
            "@Sprint", 'Sprint Details'[Sprint]         //here we select the column
        ),
        [@Sprint]                                       //the temporary column on which we apply MIN calculation
    )

Now, after we know to which sprint a ticket belongs you can connect tables using "Sprint" column:

From this point you can continue your analysis at Sprint level using "Sprint" column from "Sprint Details" table.

[ImHereToLearn]

Amazing!! Thank you very much for the detailed explanation - going the extra mile to make sure that a newbie like me understands the formula ❤️ 

You are a star, @Sergii24!!