Forum Discussion
Calculate count between two dates
Hi ,
I have three tables fact table, sprint table.
Fac Table
| Fact | ||
| ID | BuildStatDate | TriageStartDate |
| 121 | 10-11-2023 | 11-11-2023 |
| 122 | 11-12-2023 | 12-11-2023 |
| 123 | 12-12-2023 | 13-12-2023 |
| 124 | 13-12-2023 | 14-12-2023 |
| 125 | 14-12-2023 | 15-12-2023 |
| 126 | 15-12-2023 | 16-12-2023 |
| 127 | 16-12-2023 | 17-12-2023 |
| Sprint | ||
| Sprint | Start | End |
| Nov | 01-11-2023 | 30-11-2023 |
| Dec | 01-12-2023 | 31-12-2023 |
Write dax to Count id when Build Start date and Triage Start date falls between Sprint Start date and End Date using calndar table .Expected Outcome will be:
| Sprint | Count based on Build Start Date | Count based on Triage Start Date |
| Nov | 1 | 2 |
| Dec | 6 | 5 |
| Total | 7 | 7 |
Hi Anonymous
Would a measure like this help?
Build = VAR _SoM = SELECTEDVALUE( 'Sprint'[Start] ) VAR _EoM = SELECTEDVALUE( 'Sprint'[End] ) VAR _Count = COUNTROWS( FILTER( ALLSELECTED( 'FactTable'[BuildStatDate] ), 'FactTable'[BuildStatDate] >= _SoM && 'FactTable'[BuildStatDate] <= _EoM ) ) RETURN _CountCounts for Build and Triage.pbix
4 Replies
Hi Anonymous
Would a measure like this help?
Build = VAR _SoM = SELECTEDVALUE( 'Sprint'[Start] ) VAR _EoM = SELECTEDVALUE( 'Sprint'[End] ) VAR _Count = COUNTROWS( FILTER( ALLSELECTED( 'FactTable'[BuildStatDate] ), 'FactTable'[BuildStatDate] >= _SoM && 'FactTable'[BuildStatDate] <= _EoM ) ) RETURN _CountCounts for Build and Triage.pbix
Hi,
For all practical purposes, the Sprint table is the Calendar table. It should just have 3 columns - Date, Month name and Month number. Sort the Month name by the Month number. there shouldnot be a relationship between Calendar Table and the other 2 tables.
