Measure to show the average number of days between two dates by Date/Week/Month

[mark_carlisle]

I have a measure which I'm having issues getting to do what I want it to

DEV - # Days Taken to Reach Closed Won for a New MQL =
VAR __dates_table =
    ADDCOLUMNS (
        SUMMARIZE ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id] ),
        "@latest_event_date_new_mql",
            CALCULATE (
                MAX ( fact_pipeline_events[event_date] ),
                ALLEXCEPT ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id] ),
                fact_pipeline_events[total_new_mql_events] > 0
            ),
        "@earlist_event_date_closed_won",
            CALCULATE (
                MIN ( fact_pipeline_events[event_date] ),
                ALLEXCEPT ( fact_pipeline_events, fact_pipeline_events[pipeline_journey_id] ),
                dim_pipeline_event[stage_name] = "Closed Won"
            )
    )


VAR __filtered_dates_table =
    FILTER (
        __dates_table,
        NOT ( ISBLANK ( [@latest_event_date_new_mql] ) )
            && NOT ( ISBLANK ( [@earlist_event_date_closed_won] ) )
    )


VAR __date_diff_table =
    ADDCOLUMNS (
        __filtered_dates_table,
        "@date_diff", DATEDIFF ( [@latest_event_date_new_mql], [@earlist_event_date_closed_won], DAY )
    )


VAR __result =
    AVERAGEX ( __date_diff_table, [@date_diff] )

    
RETURN
    __result

Returns...

• I have two MOL's in Sep. 2021 with one converting to Closed Won after 388 days in Oct. 2022 and the other after 430 days in Nov. 2022, so the average shown in Sep. 2021 is correct.
• The other months shown are also correct because there are events generating rows of data in those months of the MQL that is Closed Won in Oct. 22.
• Oct. 22 is correct because there are events for both MQL's giving an average of 409.
• Nov. 22 is again correct because theres only events happening for the MQL Closed Won in Nov. 22.

But this is not what I want to achieve - what I would like is to show the average days taken for an MQL to convert to Closed Won and only have that value show against the month (or date, week, quarter, year - just happen to have month from my date table in this visual) in which the MQL was created. So we can answer the question

Of the MQL's created in month X what was the average time taken to convert to Closed Won?

The edited screenshot below shows what I would like to achieve.