topic Re: Count total rows within given time range in a given date range in DAX Commands and Tips

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

Jacob_Li — Wed, 04 May 2022 18:15:26 GMT

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 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.

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

Arul — Wed, 04 May 2022 19:19:51 GMT

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

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

Jacob_Li — Wed, 04 May 2022 19:37:33 GMT

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.

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

tamerj1 — Wed, 04 May 2022 19:58:45 GMT

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 ) )

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

Jihwan_Kim — Thu, 05 May 2022 06:49:04 GMT

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

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

Jacob_Li — Thu, 05 May 2022 14:02:45 GMT

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)?

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

Jacob_Li — Thu, 05 May 2022 14:10:47 GMT

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.

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

tamerj1 — Thu, 05 May 2022 14:24:47 GMT

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 ) ) )

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

Jacob_Li — Thu, 05 May 2022 15:04:47 GMT

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.

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

tamerj1 — Thu, 05 May 2022 15:11:12 GMT

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 ) ) ) )

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

Jacob_Li — Thu, 05 May 2022 15:18:43 GMT

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