Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |