Hi Community,
Please help me with following issue:
TABLES:
1. Issue-Table
| IssueKey | IssueGroup | Opened |
| 123 | Group A | 01.01.2022 |
| 124 | Group B | 01.01.2022 |
| 125 | Group B | 01.01.2022 |
| 126 | Group B | 01.01.2022 |
| 127 | Group B | 01.01.2022 |
2. History-Table
| History_IssueKey | History_Status | StatusStart | StatusEnd | Calendar Week Start | Calender Week End |
| 123 | FirstStep | 12.02.2022 | 12.02.2022 | 6 | 6 |
| 124 | Milestone | 12.02.2022 | 12.02.2022 | 6 | 6 |
| 125 | Milestone | 12.02.2022 | 18.02.2022 | 6 | 7 |
| 126 | Milestone | 12.02.2022 | 19.02.2022 | 6 | 7 |
| 127 | Milestone | 12.02.2022 | 19.02.2022 | 6 | 7 |
3. Time-Table
| Date | Calender Week |
| 12.02.2022 | 6 |
| 13.02.2022 | 6 |
| ... | ... |
| 18.02.2022 | 7 |
| 19.02.2022 | 7 |
RELATIONSHIPS:
- Issues & Time: DateOpened; Date (Active)
- Issues & History: IssueKey; History_IssueKey (Active)
- History & Time: Date; StatusStart (Inactive)
- History & Time: Date; StatusEnd (Inactive)
OBJECTIVE:
| Calender Week | Issues_MilestoneStatusStarted | Issues_MilestoneStatusEnded |
| 5 | 0 | 0 |
| 6 | 4 | 1 |
| 7 | 0 | 3 |
| ... | ... | ... |
CURRENT MEASURES:
- Issues_MilestoneStatusStarted = CALCULATE(DISTINCTCOUNT(History[History_IssueKey]), History[HistoryStatus] = "Milestone", Issues[Group]="A", USERELATIONSHIP(History[StatusStart], Time[Date])
- Issues_MilestoneStatusEnded = CALCULATE(DISTINCTCOUNT(History[History_IssueKey]), History[HistoryStatus] = "Milestone", Issues[Group]="A", USERELATIONSHIP(History[StatusEnd], Time[Date])
Somehow the result i get is this. Both measures have the same results (and somehow I've got more counts than expected).
| Calender Week | Issues_MilestoneStatusStarted | Issues_MilestoneStatusEnded |
| 5 | 0 | 0 |
| 6 | 5 | 5 |
| 7 | 10 | 10 |
| ... | ... | ... |
If I use a card visual both measures display the same value. When I use the filter pane the cards show the right value:
1. filter CalenderWeekStart = 6 --> Card shows "4"
2. filter CalenderWeekEnd = 6 --> Card shows "1"
How can i correct my measures to get the right values in the table (v. "Objective")?
Thank you!
