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)
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]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi az38 ,

     

    Thanks so much for your reply.

     

    I have this currently, and I've decided on putting a 1 in the cell if its applicable.

    They are all displaying as 1 with this formula. Can you spot anything incorrect?

     

    DueCurrentMonth =
    VAR _endDate = IF(ISBLANK([Date1]), IF(ISBLANK([Date2]), [DateUpdated],[Date2]) ,[Date1])
    RETURN

    IF(
    DATEDIFF(DATEADD(vw1[DateUpdated],-1, MONTH), _endDate, MONTH) = 0, 1,0)
    • az38's avatar
      az38
      Community Champion
      6 years ago

      Anonymous 

      i will not be able to help you without data example