I’m trying to come up with a measure to calculate the count of “Work Items ID’s” in a certain state for a selected month. The issue is that, as long as the Work item stays in the same state, there is no data available (so whenever there is no data, the state from the latest available date needs to be taken into account).
In example below I want to calculate how many work items are in state 4 in month “2023-01”
For Item 12 it is clear, but for item 10 there is no value -> looking at previous known month we see that is was (and is) in state 4
| Work Item ID | Month | State |
|---|---|---|
| 10 | 2022-09 | 1 |
| 10 | 2022-12 | 4 |
| 10 | 2023-03 | 5 |
| 10 | 2023-12 | 2 |
| 12 | 2022-10 | 2 |
| 12 | 2023-01 | 4 |
| 12 | 2023-06 | 5 |
enter image description here
I came up with a solution in Excel – multiplying the 4 rows of data for item 10 with a “calender”
Step 1) all values in the calender > 2023-01 are out of scope
Step 2) all values for Date Recalc (=month) > are out of scope
Step 3) taking the max value of the remaining values from “Date Recalc” is 2022-12 -> This is the value in the original table which has the correct state
Result of the measure should be (when selecting 2023-01) 2 (as for count of work item id’s in step 4)
PieterA is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.