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
BMassey
Frequent Visitor

Average monthly story WIP for an agile Team

Hello!  Our agile teams use Azure DevOps (ADO) to track their work.  We've connected Power BI to ADO, to pull in data and build out custom reports in Power BI.  I'm working on a report that shows average Team WIP (work in progress), by looking at when each story started and ended (or is still in progress).  I found a video online that helped me with creating a chart that shows how much WIP was in progress each day, based on the Active Date and Closed Date of each story.

 

BMassey_0-1680438999334.png

Relevant tables in the dataset:

  1. WorkItems - An entry for each work item (stories, features, epics, bugs).  For the WIP report, I only care about user stories.
  2. Calendar_ActiveDate - A calendar table that's linked to WorkItems, based on the date the storie went into an "Active" state.

 

Calculated column on our WorkItems table:

 

WIP_CloseDate =

VAR ActiveDate = WorkItems[Activated Date]
VAR ClosedDate = WorkItems[Closed Date]

VAR WIP_ClosedDate =
IF(
   LEN(ClosedDate)>0,   // WorkItem has a ClosedDate
   ClosedDate,     // SET WIP_ClosedDate = closed date on work item
   IF(
      LEN(ActiveDate)>0,    // No CloseDate exists but WorkItem has an active date
      TODAY()+1,     // SET WIP_ClosedDate = current date plus 1
      DATE(1999, 01, 01)    // IF WorkItem doesn't have a ClosedDate or ActiveDate then it shouldn't be counted in WIP
    )
)

RETURN WIP_ClosedDate
 
 
Measures:
M_WIPCountByActiveDate = COUNT(WorkItems[Work Item Id])
 
 
M_WIPCountBetweenDates =

VAR ActiveEndDate = MAX(Dim_Calendar_ActiveDate[Calendar_Date])
VAR Result =
CALCULATE(
[M_WIPCountByActiveDate],
REMOVEFILTERS('Dim_Calendar_ActiveDate'),
WorkItems[Work Item Type] = "User Story",
WorkItems[State] <> "Removed",
WorkItems[Activated Date] <= ActiveEndDate,
WorkItems[WIP_CloseDate] > ActiveEndDate
)

RETURN Result
 
 
Where I could use some help
  1. I'd like to show an average daily WIP for the month (add up the WIP count for each day divided by total number of days in the month).
  2. On the chart above that shows the WIP total per day, I'd like for a user to be able to click on an individual date and then have a table that shows the stories that were in progress on that date.  However, currently the filtering on the table gets all messed up when a date is clicked on in the chart.

 

Any assistance would be greatly appreciated.

1 ACCEPTED SOLUTION

@BMassey 

Glad I can help. Your measure looks good, and looking at it again I don't think the USERELATIONSHIP is necessary, because you already reference ActiveEndDate to the calendar table and then apply it to the WorkItems table. So I think you can remove this line from the code. 

Have you tried applying the measure as a filter to the matrix? You can do this by setting the result equal to 1. Because the table is at WorkItem level, the result is always equal to 0 or 1, where 0 the WorkItem is not included in the range and for 1 it is. 

Because you apply a measure as a filter, the filter is dynamically updated every time you make a new selection in the chart.

View solution in original post

4 REPLIES 4
Barthel
Solution Sage
Solution Sage

@BMassey 

1. You can calculate a daily average by creating a row context at day level; calculate the number of WIP per day; and take the average of it. This can be done using the AVERAGEX function. In the first part of the function you create the row context. In this case every day of the month. Assuming your calendar table is day level and the report is filtered for the current month, you can take the VALUES from the date column in your calendar table. The expression in the AVERAGEX function is then a reference to your WIP measure. Because you refer to another measure, context transition is automatically activated and you don't have to put an explicit CALCULATE function around it. For instance: 

 

Daily average =
AVERAGEX (
    KEEPFILTERS ( VALUES ( 'Calendar_ActiveDate'[Date] ) ),
    [M_WIPCountBetweenDates]
)​

 

2. Is there an active relationship between the calendar table and Workitems table? In this case, filtering the calendar table (by selecting a specific day in the chart) would automatically filter the Workitems table, which could produce unwanted results. You could choose to set the relationship to inactive. And then activate it in measures using the USERELATIONSHIP function. This gives more freedom and the possibility to filter the report the way you want.

Thanks @Barthel!  I really appreciate the guidance.  I now have the chart for monthly average, per your assistance.  I'm still struggling with filtering the table to show the correct results.  Here is what I have.

 

BMassey_0-1680461448010.png

 

I disabled the relationship between active date and the calendar table.  I then updated this measure to include the USERRELATIONSHIP function.

 

 

M_WIPCountBetweenDates = 

VAR ActiveEndDate = MAX(Dim_Calendar_ActiveDate[Calendar_Date])
VAR Result = 
    CALCULATE(
        [M_WIPCountByActiveDate],
        REMOVEFILTERS('Dim_Calendar_ActiveDate'),
        USERELATIONSHIP(WorkItems[ActivatedDate_CalendarSK], Dim_Calendar_ActiveDate[CalendarSK]),
        WorkItems[Work Item Type] = "User Story",
        WorkItems[State] <> "Removed",
        WorkItems[Activated Date] <= ActiveEndDate,     
        WorkItems[WIP_CloseDate] > ActiveEndDate
    )

RETURN Result 

 

 

I'm still stuggling connecting the dots with how I apply this to the results table that shows each user story.

@BMassey 

Glad I can help. Your measure looks good, and looking at it again I don't think the USERELATIONSHIP is necessary, because you already reference ActiveEndDate to the calendar table and then apply it to the WorkItems table. So I think you can remove this line from the code. 

Have you tried applying the measure as a filter to the matrix? You can do this by setting the result equal to 1. Because the table is at WorkItem level, the result is always equal to 0 or 1, where 0 the WorkItem is not included in the range and for 1 it is. 

Because you apply a measure as a filter, the filter is dynamically updated every time you make a new selection in the chart.

@Barthel - Applying the measure as a filter on the table did the trick.  I never realized you could do that.  Thanks again for the assistance!

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.