A measure to count items closed in custom date ranges

[Anonymous]

Hi all!

I have three tables :
Calendar

Date

01/01/2021

02/01/2021

20/01/2022

and so on

Iterations

StartDate	EndDate	IterationName
09/12/2021	22/12/2021	23.6
23/12/2021	12/01/2022	23.7
13/01/2022	02/02/2022	24.1

WorkItems

WorkItemID	Status	ClosedDate
xxx1	Closed	01/01/2022
xxx2		23/12/2021
xxx3	Open
xxx4	Closed

The objective here is to count all work items closed (status closed and have a closed date) during each of the Iterations. I want to build a visual, where the Iterations are on the X Axis and they have a number of items closed during each of them.
Kind of like this:

What should the measure to calculate this look like? How to make sure the IterationName field displays in a chronological order on the visual? Calendar Table is related to WorkItems on Date-ClosedDate. I don't know how to build relationship between Iterations and WorkItems. Or should it be related to Calendar?

WorkItems table isn't perfect and there are some missing fields.

Please help.

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

You do not need to build relationship between Iterations and WorkItems. To make sure the IterationName field order on the visual, you need to sort IterationName by StartDate(or EndDate) column.

Then create the measure, and set the type of the X-axis to "Categorical".

Measure = 
CALCULATE ( 
    DISTINCTCOUNT ( WorkItems[WorkItemID] ),
    FILTER (
        WorkItems,
        WorkItems[ClosedDate] <= MAX ( Iterations[EndDate] )
            && WorkItems[ClosedDate] >= MAX ( Iterations[StartDate] )
            && WorkItems[Status] = "Closed"
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.