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.