Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Relate 2 date fields from 2 different tables

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 su...
  • Anonymous's avatar
    Anonymous
    3 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.