Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dateadd inside datediff

Hi,

 

Could somebody please help me convert my SQL query to DAX?

 

DateDiff(month, dateadd(month, -1, DateUpdated), Isnull(Date1, IsNull(Date2, DateUpdated)))

 

Thanks in advance

 

Liam

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    try

    Column = 
var _endDate = IF(ISBLANK([Date1]), IF(ISBLANK([Date2]), [DateUpdated],[Date2]) ,[Date1])
RETURN
DATEDIFF(DATEADD([DateUpdated], -1, MONTH), _endDate, MONTH)

5 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try

    Column = 
var _endDate = IF(ISBLANK([Date1]), IF(ISBLANK([Date2]), [DateUpdated],[Date2]) ,[Date1])
RETURN
DATEDIFF(DATEADD([DateUpdated], -1, MONTH), _endDate, MONTH)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi az38,

       

      Thanks for your reply. Date 1 and Date 2 are both fields from calculation columns and its only suggesting measure values on intellisense?

       

      Also, could you please advise on how the measure would be If I was to sum total rows and get a count of rows that had this specified datediff below?

       

      Thanks in advance

       

      Liam

      • az38's avatar
        az38
        Community Champion
        6 years ago

        Anonymous 

        if your Date1 and Date2 are calculated columns my advice is to create a calculated column, not easure for datediff

         

        For second task try a technique like

        measure = 
calculate(COUNTROWS(Table), ALLEXCEPT(Table, Table[DatediffColumn]))