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:

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

10 Replies

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Jacob_Li
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

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

    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's avatar
      Jacob_Li
      Frequent Visitor
      4 years ago

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