cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
axy5093 Regular Visitor
Regular Visitor

Get count based on a date being withing a range of date from another table with relationship

Hi,

 

I need help with understanding how to get a counts based on a date range Sample PBI file with required visualization. I have a table with some issues(id), duedate and fixversion(Includes PI name. One PI has 6 sprints in it). And another table with PI schedule and another table with Sprint schedule and there is a1:* relationship from PI_Schedule to issues(fixversion) and likewise  from PI schedule to sprint schedule. What i need is count of id under each PI and when i drill down to sprint based on due date of issue i would like the count to be divided among the sprints. 

 

But i dont understand how to accomplish this relationship between due date and the sprint start date, end date.

 

Thanks for the support in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Get count based on a date being withing a range of date from another table with relationship

Hi,

 

Download the PBI file from here.

 

Hope this helps.

 

Untitled.png

9 REPLIES 9
Community Support Team
Community Support Team

Re: Get count based on a date being withing a range of date from another table with relationship

Hi @axy5093

Based on my understanding,

count of id under each PI:

uPI_1 19

uPI_2  0

uPI_3  0

uPI_4  0

uPI_5  0

uPI_6  0

 

i drill down to sprint based on due date of issue

the hierarchy should be 

SampleExporteddata[duedate]->Sprint Schedule[Sprint No]

 

the count to be divided among the sprints

How does this formula calculate?

 

Best Regards

Maggie

 

 

 

axy5093 Regular Visitor
Regular Visitor

Re: Get count based on a date being withing a range of date from another table with relationship

hI @v-juanli-msft

 

Right so when i drill down into uPI_1, instead of getting individual values for each sprint adding up to 19 i get 19 for all the sprints.

 

Any workarounds/ suggestions will be helpful!

 

Thanks,

Aishwarya 

Super User
Super User

Re: Get count based on a date being withing a range of date from another table with relationship

Hi,

 

Download the PBI file from here.

 

Hope this helps.

 

Untitled.png

axy5093 Regular Visitor
Regular Visitor

Re: Get count based on a date being withing a range of date from another table with relationship

Thank you @Ashish_Mathur This is very helpful. My understanding from your solution is that for date comparisons we will need continuous dates. Please correct me if am wrong. 

 

Regards,

Aishwarya 

Super User
Super User

Re: Get count based on a date being withing a range of date from another table with relationship

Thank you.  Yes,  that is correct.

axy5093 Regular Visitor
Regular Visitor

Re: Get count based on a date being withing a range of date from another table with relationship

Hi @Ashish_Mathur ,

 

Am back for your expertise, This is regarding the same visualization that you helped me with but this time i am trying to plot a burn down. That is at PI level i need the total planned features & delivered where as when i drill down to sprint i need a burn down of the features based on the sprint dates. 

 

Measures:

Planned feature= distinct count based on due date 

Delivered features = distinct count based on Closed date and status = "closed"

 

I understand that the logic would be TotalForEachPI - PlannedFeaturesForSprint, But I am not sure of the logic for the counts would be with measures 

 

Also if this is not a good solution i dont mind showing only burndown at sprint level with a slicer of PI But the chart would need to start with total like the image below.

Capture.PNG

Sample Power BI  with current measure and updated columns

 

 

Thank you. 

Super User
Super User

Re: Get count based on a date being withing a range of date from another table with relationship

Hi,

 

I do not understand your requirement.  Please show the result you are expecting in a tabular format with an explanation of how you arrived at those figures.  Once the Table is ready, we can simply create a chart from it.

axy5093 Regular Visitor
Regular Visitor

Re: Get count based on a date being withing a range of date from another table with relationship

Hi,

 

Taking PI = uPI_1, Total Planned =19, Total Delivered=14 The following will be the table 

Sprint NoPlannedFeatures PlannedBurndownDeliveredFeaturesDeliveredBurndown
1419-4 = 15314-3=11
2415-4= 11311-3=8
3311-3=828-2=6
428-2=626-2=4
616-1=504-0=4

 

Chart: xaxis= Sprint No. Two lines - Planned Burndown & Delivered Burndown

 

Thanks 

Super User
Super User

Re: Get count based on a date being withing a range of date from another table with relationship

Hi,

 

The total of planned is 14 (not 19).  Still not clear about your question.