Bar Chart showing last 5 sprints

[Anonymous]

Hi,

I have one table that is called Sprints and has the folowing variables: Project, SubProject, SprintNumber, StartDate, EndDate, IsEnded. 

By the other hand, I have another table called WorkItems that has all the information regarding WorkItems, e.g.: WorkItemId, WorkItemType, State, CreationDate, CloseDate, AutomatedTestCases.

Those tables are linked by SprintId. 

I need to create a bar chart that shows the number of Automated Test Cases on the last 5 Sprints as it is shown on the image below. 

I created the following measure:

Last 5 Sprint Trend =

VAR _UpperLimitDate = MAX(Iterations[StartDate_MMDDYYYY])

VAR _MaxDate = MAX(Iterations[StartDate_MMDDYYYY])

VAR _CurrentStartDate = SELECTEDVALUE(Iterations[StartDate_MMDDYYYY])

VAR _UpperLimit = MAX(Iterations[IterationNumber])

VAR _MaxSprint = MAX(Iterations[IterationNumber])

VAR _CurrentSprint = SELECTEDVALUE(Iterations[IterationNumber])

VAR _LimitDate = ISONORAFTER(_CurrentSprint,_MaxSprint)

VAR _TopSprintsSelection =

TOPN(

5,

CALCULATETABLE(

VALUES(Iterations[IterationNumber]),

Iterations[IterationNumber] <= _UpperLimit),

Iterations[IterationNumber],DESC

)

VAR vTable =

ADDCOLUMNS(

_TopSprintsSelection, "@Cases",

Distinctcount(WorkItems[IterationSK])

)

VAR _LasNSprintsCases =

SUMX(

vTable,

[@Cases]

)

VAR _Result =

IF(

_LimitDate,

BLANK(),

_LasNSprintsCases)

Return

_LasNSprintsCases

This measure shows the total number of automated test cases, but I cannot show the total by sprint number. 

Dou you know how can I create this chart?

Thanks.

[Anonymous]

Hi @v-yiruan-msft,

In the sample data you can find WorkItems and Iterations. As it its shown in the dashboard, each iteration belongs to a Domain and a Project.

Here is the link to the pbix file: https://drive.google.com/file/d/1WCO5LORPV_z86dg3-4jfVFluVYYeXpir/view?usp=sharing

Please let me know if you need further information.

Best Regards

[v-yiruan-msft]

Hi @Anonymous ,

It seems that I have no access to your file. Could you please grant the proper accss to me? And you can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft]

Hi @Anonymous ,

In order to give you a suitable solution, could you please provide some sample data in your table Iterations and WorkItems (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Best Regards