Forum Discussion
Relate 2 date fields from 2 different tables
- Anonymous3 years ago
That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:
VAR StartDate = SELECTEDVALUE ( Subscription[SubscriptionStartDate] ) VAR LicenseCreatedDate = MIN ( 'Trend'[LicenseCreatedDate] ) VAR SubId = SELECTEDVALUE ( Subscription[SubscriptionId] ) VAR _firstdupe = MAXX ( FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ), Subscription[SubscriptionStartDate] ) RETURN IF ( LicenseCreatedDate > StartDate && LicenseCreatedDate < _firstdupe, CALCULATE ( COUNTX ( 'Trend', 'Trend'[PersonId] ), 'Trend'[LicenseCreatedDate] > StartDate && 'Trend'[LicenseCreatedDate] < _firstdupe ), CALCULATE ( COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] ) ) )
Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.
That got me on the right track but a friend and I got together over lunch and we got really close using variables. One of them was a MAXX flag that would always bring back the second date in the list so we could run logic off of it. It more or less is giving us the correct answer now:
VAR StartDate =
SELECTEDVALUE ( Subscription[SubscriptionStartDate] )
VAR LicenseCreatedDate =
MIN ( 'Trend'[LicenseCreatedDate] )
VAR SubId =
SELECTEDVALUE ( Subscription[SubscriptionId] )
VAR _firstdupe =
MAXX (
FILTER ( ALL ( Subscription ), Subscription[SubscriptionId] = SubId ),
Subscription[SubscriptionStartDate]
)
RETURN
IF (
LicenseCreatedDate > StartDate
&& LicenseCreatedDate < _firstdupe,
CALCULATE (
COUNTX ( 'Trend', 'Trend'[PersonId] ),
'Trend'[LicenseCreatedDate] > StartDate
&& 'Trend'[LicenseCreatedDate] < _firstdupe
),
CALCULATE (
COUNTX ( 'Trend', 'Trend'[LicenseCreatedDate] )
)
)
Now all I need to do is remember how to turn the 1 in the below image to 8, since that 1 represents them adding another license on 9/19 but it should be summed to the 7 that came before it.
We ended up going a different route but I will mark my last one as a solution because it was close enough.