how to SUMX for datediff totals

[Roberto456]

Hello All.

I'm trying to add up the days between orders for all customers.  Below is my formula.

Days Between Orders 1:2 =

VAR DATEDIFFVAR = DATEDIFF([Invoice Date (Min)],[Invoice Date (Min) R2],DAY)

VAR TOTALTABLE = SUMMARIZE('(SQL) Cart','(SQL) Cart'[Customer_ID],"DAYS BETWEEN",DATEDIFFVAR)

RETURN

IF(HASONEVALUE('(SQL) Cart'[Customer_ID]),DATEDIFFVAR,SUMX(TOTALTABLE,DATEDIFFVAR))

As you can see its not adding up the days properly.

Does anyone know why?

[v-lili6-msft · Accepted solution]

hi  @Roberto456 

Just adjust the formula as below:

New Days Between Orders 1:2 =

VAR DATEDIFFVAR = DATEDIFF([Invoice Date (Min)],[Invoice Date (Min) R2],DAY)

VAR TOTALTABLE = SUMMARIZE('(SQL) Cart','(SQL) Cart'[Customer_ID],"DAYS BETWEEN",DATEDIFF([Invoice Date (Min)],[Invoice Date (Min) R2],DAY))

RETURN

IF(HASONEVALUE('(SQL) Cart'[Customer_ID]),DATEDIFFVAR,SUMX(TOTALTABLE,[DAYS BETWEEN]))

Result:

Regards,

Lin

[Roberto456]

Thank you 🙂

[amitchandak]

I did not get the problem completely.

Refer to this file for Way 3 and Way 4. You just have to use same table :https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0