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