DAX using earliest/max date over two tables

[SanderVeeken]

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. The courses also have a minimum number of subscribers (otherwise they're cancelled) and there is a feeling in the organization that many courses reach their minimum at the very last minimum. I want to quantify this.

What I want to calculate is basically:

AVERAGE(COURSESTARTINGDATE - MAX(EARLIEST X SUBSCRIPTION DATES)

where X is taken from the course table, and subscribers dates are filtered to the course I'm using the starting date of.

I'm just having trouble converting this to DAX, I think it's at least partly because the dates are taken from two different tables. Hopefully someone can point me in the right direction.

[johnt75 · Accepted solution]

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]

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!

[Greg_Deckler]

@SanderVeeken Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.