Forum Discussion

antlufc's avatar
antlufc
Frequent Visitor
3 years ago

Days Between Dates DAX

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...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
antlufc
Frequent Visitor
3 years ago

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


  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      antlufc
      Frequent Visitor
      3 years ago

      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.