Using DATEDIFF WHERE ColumnD = XX??

[TimJones95]

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 Leads'[Modified],MONTH) WHERE Status = 'Won'

Thanks in advance.

[v-yuta-msft]

@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]

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?

[v-yuta-msft]

@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

[parry2k]

@TimJones95  add new column with following DAX

New Column (Diff Months) = 
IF( Table1[Status] = "Won", DATEDIFF( Table1[Created], Table1[Modified], MONTH ) )