Forum Discussion

TimJones95's avatar
TimJones95
New Member
7 years ago

Using DATEDIFF WHERE ColumnD = XX??

Hi,   Does anyone know how to take the DATEDIFF of two date columns where another column equals a specific value.   In my pseudo code :-)   CycleLength = (DATEDIFF('Sales Leads[Created],'Sales ...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

TimJones95 ,

 

You may create a measure using DAX as below:

CycleLength =
CALCULATE (
    DATEDIFF ( 'Sales Leads'[Created], 'Sales Leads'[Modified], MONTH ),
    FILTER ( 'Sales Leads', 'Sales Leads'[Status] = "Won" )
)

Community Support Team _ Jimmy Tao

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

TimJones95's avatar
TimJones95
New Member
7 years ago

Thanks for the reply. However, when I try and run this it comes up with an error.

 

The actual error is 'A single value for column 'Created' in table 'Sales Leads' cannot be determined. This can happen when a measure formula refers o a column that contains many values without specifying an aggregation sum, min or max to get a single result'

 

Same issue for 'Modified' too. Is it because these are dates?

 

Any ideas?

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    TimJones95  add new column with following DAX

     

    New Column (Diff Months) = 
IF( Table1[Status] = "Won", DATEDIFF( Table1[Created], Table1[Modified], MONTH ) )
  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    TimJones95 ,

     

    Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

     

    Regards,

    Jimmy Tao