Count rows based on dates from 2 separate tables

[PaulG572]

Hi community,

I am struggling to create a calculated column that counts activities per account between dates from different tables and was wondering if anyone can help? The table schemas are as below with a 1 to many relationship between tblAccounts(AccountID) and tblActivities(AccountID).

tblAccounts

AccountID BIGINT PK

OpenDate DATE

DaysOpen INT <- Calculated column DATEDIFF ( 'tblAccounts'[OpenDate], TODAY (), DAY )

tblActivities

ActivityID BIGINT PK

AccountID BIGINT FK

ActivityTypeID INT

ActivityDate DATE

What I am looking to achieve is if DaysOpen >= 30 then count activities between OpenDate and OpenDate + 30 days. I have tried variations on the below but to no avail.

IF ( 'tblAccounts'[DaysOpen] >= 30 , CALCULATE ( COUNTROWS('tblActivities') , DATESBETWEEN('tblActivities'[ActivityDate],'tblAccounts'[OpenDate],DATEADD('tblAccounts'[OpenDate],30,DAY)) , 0 )

Any help will be greatly appreciated.

Many thanks

Paul

[Anonymous · Accepted solution]

[# Activities (30D)] = -- column in tblAccounts
var __daysDiff = 30
var __daysOpen = tblAccounts[DaysOpen]
var __account = tblAccounts[AccountID]
var __openDate = tblAccounts[OpenDate]
var __endDate = __openDate + __daysDiff
var __activityCount =
	COUNTROWS(
		FILTER (
			tblActivities,
			tblActivities[AccountID] = __account
			&& tblActivities[Date] >= __openDate
			&& tblActivities[Date] <= __endDate 
		)
	)
RETURN
	if(
		__daysOpen >= __daysDiff,
		__activityCount
	)

Best

D.

[PaulG572]

@Anonymous many apologies I thought i'd already thanked you for this, so at last thank you it worked a treat