Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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-202...
Data Protection
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Ashish_Mathur
Super User
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    Understood. Thanks for the reply!!