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 )
            )
    )
)
Arul's avatar
Arul
Super User
4 years ago

Jacob_Li ,

 

Check if it does helps or not?

 

Count customer = 
Var _Mindate = DATEVALUE( "3/5/2022")
Var _Maxdate = DATEVALUE( "4/5/2022")

Var _Count = CALCULATE(COUNTROWS('Customer Count'),'Customer Count'[Start Date]>=_Mindate,'Customer Count'[End Date]<=_Maxdate)
return _Count

 

Thanks,

Arul

  • Jacob_Li's avatar
    Jacob_Li
    Frequent Visitor
    4 years ago

    Thank you, but I am afraid the restriction might be correct. Because I want to evaluate each individual date between 05/03/2022 and 05/04/2022, I need to compare these two dates with start date and end date for twice, instead of comparing 05/03/2022 with StartDate and 05/04/2022 with EndDate. 

     

    Thanks anyway.