[DAX] Cumulative Average with DAX

To simplyfy the solution to you problem I would suggest to model your data with a new Fact table that combines the information in TableA with TableB.
So if the Date in TableA is on or after the ApplyDate in TableB you get the Category and corresponding Gap in separate columns in the new Fact table.
You can create this Fact table as a new calculated table with the following DAX expression:

FactTable =

GENERATEALL (

    TableA,

    VAR curDate =

        RELATED ( 'Calendar'[Date] )

    RETURN

        CALCULATETABLE (

            TableB,

            TableB[ApplyDate] <= curDate,

            REMOVEFILTERS ( 'Calendar' )

        )

)

 

Once you have that table you create a relationship with FactTable[Date] and Calendar[Date].

The three measures you need to create the required matrix will the be the following:

 

Cumulative Qty =

 CALCULATE(

     DISTINCTCOUNT(FactTable[ID]),

     DATESYTD('Calendar'[Date])

 )

 

Cumulative Sum =

CALCULATE(

    SUM(FactTable[Gap]),

    DATESYTD('Calendar'[Date])

)

 

Cumulative Average =

DIVIDE(

    [Cumulative Sum],

    [Cumulative Qty]

)

 

The result will be this: