cancel
Showing results for
Did you mean:
Highlighted
Occasional Visitor

## 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

Super User

## Re: Count rows based on dates from 2 separate tables

[# 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.

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,021)