Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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...
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.
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
6 |