Days Between Dates DAX

[antlufc]

Hi,

I am looking to get days between certain dates to then show as an average in a matrix table / table. I would like to use a DAX measure and not add any further columns to my model.

I have two tables that have a relationship, Dimension Lead to Opp and FACT Lead to Opp, relationship view is available on attached PBI file.

I would like to see days between the 3 dates below:

1. Start Opportunity Date – Start MQL Date
2. Close Date – Start MQL Date
3. Close Date – Start Opportunity Date

I would like to show 0 or blank if the Start MQL date is blank to be able to exclude them from the average calculation. As it has not entered the criteria for the calculation.  Also blanks if any of the three fields are blank I would like to show blank as the result so it does not impact averages when I am giving a total number.

https://1drv.ms/u/s!AivZWzcfJJzngSP8be8JmJ8xq91l?e=MYiZ33 

Expected Results attached. 

[eliasayy]

hello i know most of the solution but i dont know how to ignore blanks 

Measure sql to opp = 
VAR _mql = LASTDATE('DIMENSION LEAD TO OPP'[start_mql_date])
VAR _opp = FIRSTDATE('DIMENSION LEAD TO OPP'[start_opportunity_date])

RETURN
COUNTROWS(CALENDAR(_mql,_opp))-1

but for when opp is blank or any date is blank it gives error

[antlufc]

Thanks for the suggestion, it has not worked due to "Calendar function can not be Blank value"

[v-rzhou-msft]

Hi @antlufc ,

Firstly, please make sure [start_mql_date] and [start_opportunity_date] in "DIMENSION LEAD TO OPP" and [close_date] in "FACT LEAD TO OPP" are date type.

Then you can create measures as below to achieve your goal.

Latest MQL Date = 
VAR _DATE = MAX('DIMENSION LEAD TO OPP'[start_mql_date])
RETURN
IF(_DATE = BLANK(),"Blank",_DATE)

First Opp Date = 
VAR _DATE = MAX('DIMENSION LEAD TO OPP'[start_opportunity_date])
RETURN
IF(_DATE = BLANK(),"Blank",_DATE)

Close Date = 
VAR _DATE =
CALCULATE(MAX('FACT LEAD TO OPP'[close_date]),FILTER(ALL('FACT LEAD TO OPP'),'FACT LEAD TO OPP'[lead_to_opportunity_id] = MAX('DIMENSION LEAD TO OPP'[id])))
RETURN
IF(_DATE = BLANK(),"Blank",_DATE)

MQL to Opp Days = 
VAR _DIFF = DATEDIFF(MAX('DIMENSION LEAD TO OPP'[start_mql_date]),MAX('DIMENSION LEAD TO OPP'[start_opportunity_date]),DAY)
RETURN
IF(_DIFF = BLANK(),"Blank",_DIFF)

MQL to Close = 
VAR _MQL = MAX('DIMENSION LEAD TO OPP'[start_mql_date])
VAR _CLOSE = CALCULATE(MAX('FACT LEAD TO OPP'[close_date]),FILTER(ALL('FACT LEAD TO OPP'),'FACT LEAD TO OPP'[lead_to_opportunity_id] = MAX('DIMENSION LEAD TO OPP'[id])))
VAR _DIFF = DATEDIFF(_MQL,_CLOSE,DAY)
RETURN
IF(_DIFF = BLANK(),"Blank",_DIFF)

Opp Created to Close = 
VAR _Opp = MAX('DIMENSION LEAD TO OPP'[start_opportunity_date])
VAR _CLOSE = CALCULATE(MAX('FACT LEAD TO OPP'[close_date]),FILTER(ALL('FACT LEAD TO OPP'),'FACT LEAD TO OPP'[lead_to_opportunity_id] = MAX('DIMENSION LEAD TO OPP'[id])))
VAR _DIFF = DATEDIFF(_Opp,_CLOSE,DAY)
RETURN
IF(_DIFF = BLANK(),"Blank",_DIFF)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[antlufc]

I have an issue where the start mql date and start opp date are the same date ths is returning blanks however in this instance i would like it to return 0.  

This would also be the case when start opp date and close opp date would be the same. 

[AnkitKukreja]

Hi! @antlufc 

I am unable to access your file. Can you copy paste your sample data.

[antlufc]

I have amended the link can you let me know if you can now access?