Dateadd inside datediff

[Anonymous]

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

[az38 · Accepted solution]

Hi @Anonymous 

try

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

[Anonymous]

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]

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

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]

@Anonymous 

i will not be able to help you without data example