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

[Jacob_Li]

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:

1. The active period of the customer (StartDate & EndDate)
2. The time period we are interested in

As can be seen on the screenshot above, I want to calculate the total headcounts within the date range 2022-05-03 to 2022-05-04.

Since it only includes two days, the calculation is pretty straightfoward: on day 1 we have Customer 5 and Customer 6 meeting the criteria, on day 2 we still have these two customers active. Thus the results should be: 2+2=4

Based on the idea of v-yingjl

https://community.powerbi.com/t5/Power-Query/Count-rows-if-between-two-time-slots/td-p/1424481 and other posts,

I wrote the measure as follows:

Headcounts=

var calendardate=max('Calendar'[Date])

var num_ava=calculate(countrows(filter(all('fact'),calendardate>='fact'[StartDate] && calendardate<='fact'[EndDate])))

I am wondering how could I get the total headcounts based on the measure. Any thoughts and ideas are appreciated.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below in order to simplify the sample.

I suggest having a calendar table as a dimension table.

I hope the below measure can help to have an idea to create a similar measure and apply it to your data model.

Customers count measure: =
COUNTROWS (
    FILTER (
        Data,
        Data[StartDate] <= MAX ( 'Calendar'[Date] )
            && Data[EndDate] >= MIN ( 'Calendar'[Date] )
    )
)

[Jacob_Li]

Thanks for your reply. I took a screenshot from your pbix file.

As you could see, two dates are selected: 2022/12/30 and 2022/12/31, is there any way that I could get the value of 2, which is the sum of the column of the calculated measure (1+1)?

[tamerj1]

Hi @Jacob_Li 

assuming no relationships invloved, you may try

Headcounts =
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]

Thank you for your reply. Is there any way that I could get the sum of the "column" of headcounts measure from the table visual below. I am pretty sure I am very close to it.

[tamerj1]

@Jacob_Li 

Try

Headcounts =
SUMX (
    CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( 'fact'[StartDate] ) ),
    CALCULATE (
        VAR SelectedDates =
            VALUES ( 'Calendar'[Date] )
        VAR ActiveDates =
            CALENDAR ( 'fact'[StartDate], 'fact'[EndDate] )
        RETURN
            IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0 )
    )
)

[Jacob_Li]

@tamerj1 

By calendar('fact'[StartDate],'fact'[EndDate]), do you mean by CALENDAR (calculate(max('data'[startdate]),allexcept('data','Data'[Customer])) , calculate(max('data'[EndDate]),allexcept('data','Data'[Customer])) )? Because otherwise this measure is not working properly.

By replacing the underlined part, the measure is written as follows

Headcounts =
SUMX (
    CROSSJOIN ( VALUES ( 'Calendar'[Date] ), VALUES ( 'fact'[StartDate] ) ),
    CALCULATE (
        VAR SelectedDates =
            VALUES ( 'Calendar'[Date] )
        VAR ActiveDates =
            CALENDAR (calculate(max('data'[startdate]),allexcept('data','Data'[Customer])) , calculate(max('data'[EndDate]),allexcept('data','Data'[Customer])) )
        RETURN
            IF ( COUNTROWS ( INTERSECT ( SelectedDates, ActiveDates ) ) > 0, 1, 0 )
    )
)

However, the result is not as expected. 

It gives the total 12 instead of 2.

[tamerj1 · Accepted solution]

@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]

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

[Arul]

@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]

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.