The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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
Solved! Go to 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.
[# 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.
@Anonymous many apologies I thought i'd already thanked you for this, so at last thank you it worked a treat
User | Count |
---|---|
39 | |
36 | |
30 | |
29 | |
21 |
User | Count |
---|---|
64 | |
40 | |
32 | |
18 | |
16 |