Jira PowerBI Connector App: Lookup sprint based on issue created date and sprint start and end dates

[SunnySana]

I am using PowerBI's connector app for Jira. I have two tables: (1) table of Jira issues and 2) table of sprints. I need to use the date the issue was created (on table 1) and determine which sprint it was created in given the start and end date of the sprint (on table 2). 

Table 1: Issues

Table 2: Sprints

@Greg_Deckler , I saw your post on For and While Loops in Dax, and I saw your YouTube video on the same (very insightful, by the way. Thank you). I thought that perhaps a loop would help me here, as I have hundreds of issues to "loop" through to determine which two dates the created date falls between. But, as I was reading the article and watching the video, it seemed like this wasn't the right approach. Any ideas on how to tackle this issue?

[SunnySana]

Oh wait!! I think I got it!! I think using a modification of the formula you showed in your video worked!! Here's the magical formula: 

my beautiful working formula for between two dates = 

    CALCULATE(countx(filter('Issues - CreatedDates','Issues - CreatedDates'[CREATED] >= max('Sprints - Dates'[START_DATE]) && 'Issues - CreatedDates'[CREATED] <= max('Sprints - Dates'[END_DATE]) && 'Issues - CreatedDates'[ISSUE_TYPE_NAME] = "QA BUG"),'Issues - CreatedDates'[ISSUE_KEY]),CROSSFILTER('Issues - CreatedDates'[CREATED],'Sprints - Dates'[START_DATE],none))

And this is what I was able to create!

Thank you so so much @amitchandak !!

[amitchandak · Accepted solution]

@SunnySana , refer if userelationship and between date code can be used

Refer to how to deal with between dates

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[SunnySana]

Hi @amitchandak - 

So sorry I've a  long time to respond. I looked at the references you provided, but I'm stuck. You used a Date table where there is only one of each date (ie, only one January 1, 2015. In my problem, I think the equivelant of that date table would be my table that has the Issue Type and the Created Date, as shown below. 

As  you can see, there are multiples of dates, ie, 7/3/2023. I'm not sure if this is what is causing the issues. 

Also, in your example, you have an Employee Records table, which as the start date and end date. I think the equivalent of that in my problem would be the Sprint - Dates table, where I have the sprint name and start and end dates, as shown below:

In your video, at 4 minutes and 8 seconds, you show a table where each date has a count of employees. My problem is that my formula only pulls in the issues that were created on either the start or the end date, depending on which I specify in the formula. It doesn't pull in any of the issues with created dates between the start and end dates, as shown below:

Pulls in issues with Created Date equal to Start Date: 

issues = calculate(count('Issues - CreatedDates'[ISSUE_KEY]),USERELATIONSHIP('Sprints - Dates'[START_DATE],'Issues - CreatedDates'[CREATED]))

Pulls in Issues with Created Date equal to End Date: 

issues = calculate(count('Issues - CreatedDates'[ISSUE_KEY]),USERELATIONSHIP('Sprints - Dates'[End_DATE],'Issues - CreatedDates'[CREATED]))

You mentioned using Between Dates. How do I use Between dates in conjunction with the UserRelationship function in order to get a count of all the issues created between the start and end dates, based on Created date?