Calculate count between two dates

[Anonymous]

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

[gmsamborn · Accepted solution]

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
    _Count

Counts for Build and Triage.pbix

[Anonymous]

Thanks , just wanted to check how we can implment this using calendar table.Because i need calendar table which filter both Build and Triage.

[Ashish_Mathur]

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.

[Anonymous]

Understood. Thanks for the reply!!