Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
antlufc
Frequent Visitor

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 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. 

antlufc_0-1666692209875.png

 

 

 

 

6 REPLIES 6
eliasayy
Impactful Individual
Impactful Individual

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

 

Screenshot 2022-10-25 190551.png

 

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

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


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.

RicoZhou_0-1666776788735.png

 

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.

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
Super User
Super User

Hi! @antlufc 

 

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

 

If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.
Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.