topic Re: Dateadd inside datediff in DAX Commands and Tips

Dateadd inside datediff

Anonymous — Fri, 20 Mar 2020 09:28:00 GMT

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

Re: Dateadd inside datediff

az38 — Fri, 20 Mar 2020 09:47:55 GMT

Hi Anonymous

try

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

Re: Dateadd inside datediff

Anonymous — Fri, 20 Mar 2020 09:57:04 GMT

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

Re: Dateadd inside datediff

az38 — Fri, 20 Mar 2020 10:01:59 GMT

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

Re: Dateadd inside datediff

Anonymous — Fri, 20 Mar 2020 10:13:21 GMT

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)

Re: Dateadd inside datediff

az38 — Fri, 20 Mar 2020 10:14:26 GMT

Anonymous

i will not be able to help you without data example