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
axy5093
Helper II
Helper II

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

Hi,

 

Download the PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

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

 

 

 

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 

Hi,

 

Download the PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Thank you.  Yes,  that is correct.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.