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