topic Re: Days Between Dates DAX in DAX Commands and Tips

Days Between Dates DAX

antlufc — Tue, 25 Oct 2022 15:33:41 GMT

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:

Start Opportunity Date – Start MQL Date
Close Date – Start MQL Date
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.

Re: Days Between Dates DAX

AnkitKukreja — Tue, 25 Oct 2022 15:09:44 GMT

Hi! antlufc

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

Re: Days Between Dates DAX

antlufc — Tue, 25 Oct 2022 15:34:36 GMT

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

Re: Days Between Dates DAX

Anonymous — Tue, 25 Oct 2022 16:06:34 GMT

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

Re: Days Between Dates DAX

antlufc — Wed, 26 Oct 2022 07:57:56 GMT

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

Re: Days Between Dates DAX

Anonymous — Wed, 26 Oct 2022 09:33:22 GMT

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.

Re: Days Between Dates DAX

antlufc — Wed, 26 Oct 2022 10:03:35 GMT

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.