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

 

Thanks in advance.

Need Help

4 Replies

  • 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 ) )