Forum Discussion

Jacob_Li's avatar
Jacob_Li
Frequent Visitor
4 years ago
Solved

Count total rows within given time range in a given date range

I am working on a measure to get the total headcounts of customers within a given time range. But I need to consider two time ranges: The active period of the customer (StartDate & EndDate) The ti...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Jacob_Li 

    My code wat not correct. Sorry I'm typing on the phone

    try this one

    Headcounts =
SUMX (
    CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( 'fact'[StartDate] ) ),
    CALCULATE (
        VAR SelectedDates =
            VALUES ( 'Calendar'[Date] )
        RETURN
            SUMX (
                'fact',
                VAR ActiveDates =
                    CALENDAR ( 'fact'[StartDate], 'fact'[EndDate] )
                RETURN
                    IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0 )
            )
    )
)
tamerj1's avatar
tamerj1
Community Champion
4 years ago

Jacob_Li 

My code wat not correct. Sorry I'm typing on the phone

try this one

Headcounts =
SUMX (
    CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( 'fact'[StartDate] ) ),
    CALCULATE (
        VAR SelectedDates =
            VALUES ( 'Calendar'[Date] )
        RETURN
            SUMX (
                'fact',
                VAR ActiveDates =
                    CALENDAR ( 'fact'[StartDate], 'fact'[EndDate] )
                RETURN
                    IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0 )
            )
    )
)
Jacob_Li's avatar
Jacob_Li
Frequent Visitor
4 years ago

It works! Thank you! Also lots of thanks for all the replies under this post.