Setup:
I have 2 tables with a Many to Many relationship on a subscriptionid. This is necessary because in the first table, there can be multiple lines with the same subscriptionid due to the same subscriptionid having multiple start dates (This is due to how our system works when they buy more licenses, those licenses have their own start date). This table tells us how how many licenses they have and what dates those licenses start at.
The other table is a trend table, containing every personid and the subscriptionid they belong to. Since its a trend table, you can have multiple of the same person/subscriptionid because I am keeping track of changes to their accounts and licenses. Its complicated but necessary for how we do business. I keep track of each change by using a ValidFrom and ValidTo date field.
Example: If a PersonId goes from being a Subscription Manager to an Admin, I track that with flags and what dates. When a change is found in the DB, the ValidTo line is updated and a new line is created with a new ValidFrom.
The Problem
What I need to do is relate the ValidFrom and ValidFrom to the SubscriptionStartDate and SubscriptionEndDate in the first table. I Have the Subscription table related to the Trend table using the SubscriptionId.
What I tried was a Calculate where I perform my COUNT on the PersonId field (In Trend Table) and then in the filters, I want to say this:
ValidFrom > SubscriptionStartDate AND ValidTo > SubscriptionEndDate (This is because the "current" entries in the Trend table have a ValidTo of 12-31-9999). However, when I do this, it doesnt recognize those fields as valid.
Subscription Table
TrendTable
Relationship - its related on SubscriptionId
Trying to do something like this
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.
