Forum Discussion

PaulG572's avatar
PaulG572
New Member
7 years ago
Solved

Count rows based on dates from 2 separate tables

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's avatar
    Anonymous
    7 years ago
    [# 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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    [# 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's avatar
      PaulG572
      New Member
      6 years ago

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