Forum Discussion

SanderVeeken's avatar
SanderVeeken
Icon for Helper III rankHelper III
3 years ago
Solved

DAX using earliest/max date over two tables

Hello everyone,   I have a datamodel of courses with subscribers, each in their own table. They're linked by the unique course ID. The subscriptions have dates, and the courses have starting dates....
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Average days before course starts =
AVERAGEX (
    'Courses',
    VAR FirstSubscriptions =
        TOPN (
            'Courses'[Min subscribers],
            RELATEDTABLE ( 'Subscriptions' ),
            'Subscriptions'[Subscription date], ASC
        )
    VAR LatestSubscription =
        MAXX ( FirstSubscriptions, 'Subscription date' )
    RETURN
        'Courses'[Start date] - LatestSubscription
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

Average days before course starts =
AVERAGEX (
    'Courses',
    VAR FirstSubscriptions =
        TOPN (
            'Courses'[Min subscribers],
            RELATEDTABLE ( 'Subscriptions' ),
            'Subscriptions'[Subscription date], ASC
        )
    VAR LatestSubscription =
        MAXX ( FirstSubscriptions, 'Subscription date' )
    RETURN
        'Courses'[Start date] - LatestSubscription
)
  • SanderVeeken's avatar
    SanderVeeken
    Icon for Helper III rankHelper III
    3 years ago

    This worked perfectly, and it helped me refine my logic; in some cases courses start even though the minimum hasn't been reached (management decides there are other important reasons etc.) and I got weird values there; I added an if as follows:

    Gem Cursus compleet dagen vooraf = 
AVERAGEX(
    'Courses',
    VAR FirstSubscriptions =
        TOPN(
            'Courses'[MinSubs],
            RELATEDTABLE('Subscribers'),
            Subscribers[Subscriptiondate], ASC
        )
    VAR LastSubscription =
        MAXX(FirstSubscriptions, Subscribers[Subscriptiondate])
    RETURN
    IF ('Courses'[MinSubs]>COUNTROWS(FirstSubscriptions),
        Courses[Startingdate]-TODAY(),
        Courses[Startingdate] - LastSubscription)
)

    I need to discuss with the team if this is what they want to see, thanks for your help getting me this far!