Forum Discussion
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...
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 ) ) ) )
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.
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 )
)
)
)It works! Thank you! Also lots of thanks for all the replies under this post.